TotalMTD/TotalYTD Question

[knotty150]

Hi All

I'm trying to produce a graph which compares 2014 to 2015 sales for the month of March (1 line per year).

I've got the 2014 line displaying quite nicely using:

2014:=TOTALYTD(DISTINCTCOUNT([INVOICE No.]),'2014'[DATE],"31-03")

So thought I'd use the same formula for 2015:

2015:=TOTALYTD(DISTINCTCOUNT([INVOICE No.]),'2015'[Date],"31-03")

Problem with the 2015 line though is although it displays the correct no. sales, the line extends to the end of the X axis, even though we're only 10 days into March 2015 (as I write this). I'd like the line to only display up until the 10th March if possible?

Does that make sense?

Any help would be greatly appreciated!

Thanks

Rich

 

[ImkeF]

Hi Rich,
try it with this measure:

TotalMTD:=IF(MAX('Date'[Date])>TODAY();BLANK();TOTALMTD(DISTINCTCOUNT([Invoice No.]);'Date'[Date]; 'Date'[Date]<TODAY()))

If you put Year from your date-table into columns, days in rows and month in report filter it should bring the desired comparison of your month's figures by day up until the current calendar date. For a monthly comparison your just repalce days in rows with months.

Advantage here is that you don't need to adjust your measure at any time (e.g. year change).