DAX Calculate Year To Date - Year to Previous Month (year change)

Question

Trying to figure out how to calculate the equivalent to YTD but up til previous month when year changes.

For example:

Date    | Value
2018-10 | 100 
2018-11 | 100 
2018-12 | 100 
2019-01 | 100
2019-02 | 100
2019-03 | 100

Results expected:

When 2019-03 is selected

YTD = 300 (accumulated from 2019-01- to 2019-03)

Previous month accumulated = 200 (accumulated from 2019-01 to 2019-02)

When 2019-01 is selected

YTD = 100

Previous month accumulated = 300 (accumulated from 2018-10 to 2018-12)

I've tried:

Value_Accum_PreviousMonth:=TOTALYTD([Value];Calendar[Date]) - [Value]

but with the change of year, it doesn't work

Any ideas?

Is your date column filled with actual dates? Or are they strings? — Joe Gravelyn
Actual dates. It's a Calendar table. Works just fine for the other months. As January is special case where it has to sum all months from previous year, I have no idea how to write the formula. — Ricardo Diaz

Joe Gravelyn Joe Gravelyn · Accepted Answer · 2019-04-05T23:38:22

Disclaimer - I created this solution using Power BI. The formula should transfer over to Excel/PowerPivot, but it may require some minor tweaks.

While the time intelligence functions of DAX are super useful, I seem to be drawn towards the manual approach of calculating YTD, prior year, etc. With that in mind, this is how I would solve your problem.

First, I would make a measure that is a simply sum of your Value column. Having this measure is just nice down the road; not an absolute necessity.

Total Value = SUM(Data[Value])

Next, for use to calculate YTD manually based of the prior month, we need to know two things, 1) what is the target month (i.e. the prior month) and 2) what is the year of that month.

If you are going to use those values anywhere else, I would put them into there own measure and use them here. If this is the only place they will be used, I like to use variables to calculate these kinds of values.

The first value we need is the selected (or max date in cases of no selection).

VAR SelectedDate = MAX(Data[Date])

With that date, we can calculate the TargetYear and TargetMonth. Both of them are simple IF statements to catch the January/December crossover.

VAR TargetYear = IF(MONTH(SelectedDate) = 1, YEAR(SelectedDate) - 1, YEAR(SelectedDate))
VAR TargetMonth = IF(MONTH(SelectedDate) = 1, 12, MONTH(SelectedDate) - 1)

Having all of the values we need, we write a CALCULATE statement that filters the data to the TargetYear and month is less than or equal to TargetMonth.

CALCULATE([Total Value], FILTER(ALL(Data), YEAR([Date]) = TargetYear && MONTH([Date]) <= TargetMonth))

Putting it all together looks like this.

Prior Month YTD = 
VAR SelectedDate = MAX(Data[Date])
VAR TargetYear = IF(MONTH(SelectedDate) = 1, YEAR(SelectedDate) - 1, YEAR(SelectedDate))
VAR TargetMonth = IF(MONTH(SelectedDate) = 1, 12, MONTH(SelectedDate) - 1)

RETURN
    CALCULATE([Total Value], FILTER(ALL(Data), YEAR([Date]) = TargetYear && MONTH([Date]) <= TargetMonth))

DAX Calculate Year To Date - Year to Previous Month (year change)

1 Answers