Calculate YTD value previous month, only if within same year

[xiloid]

Hello guys,

I’ve got a measure, that calculates some YTD values using TOTALYTD.

Value, YTD:=TOTALYTD([Value];’Calendar'[Date])

I would like to calculate this YTD value for the previous month.

Value, YTD, PrevM:=CALCULATE([Value, YTD];PREVIOUSMONTH(‘Calendar'[Date]))

However, if I am now in January 2017, it will calculate the December 2016 YTD value.

How do I set the calculation for the YTD value previous month, so it only calculates it only if the previous month is in the same year?

 

[Chihiro]

Is this PowerPivot DAX question?

Edit: Hit submit before completing...

So what should be returned for previous month? A zero?

 

[xiloid]

Is this PowerPivot DAX question?

Edit: Hit submit before completing...

So what should be returned for previous month? A zero?

Hi Chihiro,

Yes, exactly, it's DAX in PowerPivot.

It should return 0 or BLANK(), doesn't matter.

 

[Chihiro]

You'd use DATEBETWEEN and DATEADD.

I can't test it right now as I don't have PowerQuery at this machine. But read point #8 in the link.
https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

Or if you have calendar table (from your formula I think you do). Just add additional filter argument in Calculate to say YEAR = CurrentYear.

Below link is also helpful.
http://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/

 

[xiloid]

You'd use DATEBETWEEN and DATEADD.

I can't test it right now as I don't have PowerQuery at this machine. But read point #8 in the link.
https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

Or if you have calendar table (from your formula I think you do). Just add additional filter argument in Calculate to say YEAR = CurrentYear.

Below link is also helpful.
http://www.sqlbi.com/articles/custom-year-over-year-calculation-in-dax/

Hi Chihiro,

this is what I ended up with and it seems to be working:

Value, YTD, PrevM:=IF(YEAR(LASTDATE('Calendar'[Date]))-YEAR(LASTDATE(PREVIOUSMONTH('Calendar'[Date])))=0;CALCULATE([Value, YTD];PREVIOUSMONTH('Calendar'[Date]));BLANK())

Not sure about the syntax of your solutions with YEAR=CurrentYear though...