There are a few ways to go here....where I'm assuming your Date Field is a real date.
Firstly you can tick this option....
Dates (and Bins) are "range aware" pills (As they know their Min, Max and Increment), so this pseudo-fills the missing values, so we can access them.
Once we have this you can right click and select format on your measure, and change the pane option to this....
OR you could create a formula like this (and just put in on the canvas in place of your measure)
IF ISNULL(SUM([Sales])) THEN 0
ELSE SUM([Sales]) END
Hope that helps.
Fill Nulls with Zeros.twbx 1,015.3 KB
Thank you very much for looking into this and this did take me somewhere however the "MM-YY" you see there its not a real date its a string derived from a "date dimension" and mixed with string function. see below.
'Q'+RIGHT(STR(DATENAME('quarter',[Latest Closure Request Date])),2)+'-'+
RIGHT(DATENAME('year',[Latest Closure Request Date]),2)
LEFT(DATENAME('month',[Latest Closure Request Date]),3)+ '-'
+RIGHT(Str(DATEPART('year' ,[Latest Closure Request Date])),2)
these are 2 calc which is used in a parameter and the user value are "monthly" and "quarter" user and choose either of them to choose the type of trend they would like to see.
So actually you don't need the 'Show Missing Values' bit (...I just assumed we needed that, to get the extra/missing marks, but apparently not!)
In which case your string versions should just work, with the NULL as 0, and "Show at Default Values"
Let me know if that doesn't do the trick for you.
Thanks it worked .