CODEWITHSUNDEEP
powerbidax
vanHohenheim
vanHohenheim

Reputation: 127

DAX Calcuate rolling sum

I have a problem with calculating measure that sums values for 3 previous periods. Below I attach sample fact table and dict table to show problem I am facing.

date customer segment value
01.01.2021 1 A 10
02.01.2021 1 A 10
03.01.2021 1 A 10
04.01.2021 1 A 10
01.01.2021 2 B 20
02.01.2021 2 B 30
03.01.2021 2 B 40

dict table:

segment segment_desc
A Name of A
B Name of B

Approach I have taken:

last 3 value = 
VAR DATES = DATESINPERIOD(facts[date],LASTDATE(facts[date]), -3,MONTH)
RETURN CALCULATE([sum value], DATES)

It produces correct results as long as there is at least one record for April. When I use filter on segment_desc = 'B' It produces result as I attached - so we see result in April equals 20, which is obviously not what I wanted. I would expect it to be 50.

enter image description here

Upvotes: 0

Views: 155

Answers (1)

RADO
RADO

Reputation: 8148

Answer to the main question: time intelligence functions like DATESINPERIOD require a proper calendar table, because they expect continuous dates, without gaps.

Answer to the follow-up question "why the measure shows value for January?"

It's a bit tricky. First, notice that LASTDATE in this filter context returns blank:

enter image description here

So, your DAX measure then becomes this:

last 3 value = 
VAR DATES = DATESINPERIOD(facts[date], BLANK(), -3,MONTH)
RETURN CALCULATE([sum value], DATES)

Blank - 3 month does not make sense, so the way DAX resolves this: it replaces BLANK with the first (min) date in the table. In this case, it's 1/1/2021. Then it goes back 3 months from that date. As a result, the final measure is:

last 3 value = 
CALCULATE([sum value], {2020-11-01, 2020-12-01, 2021-01-01 })

Since you have no data prior to 2021-01-01, the final result shows only January values.

Upvotes: 1

Related Questions