DAX Query ( Using FIlter and MAX function ), calculate Total Sales for the last running 30 days

1
votes

I am new to DAX and encountered a measure as below, 

30 Day Running Total = CALCULATE([Total Sales], 
    FILTER (ALL (Dates), Dates[Date]>(Dates[Date]) -30 && Dates[Date] <= (Dates[Date] )))

i.e. to calculate Total Sales for last 30 days in a cumulative way for the data from 1st January 2018 to 30 December 2021, the above measure i am not able to understand

My understanding is as below, please let me know where I am moving in wrong direction

  1. FILTER ( ALL(Dates) -> Removes all filters means take date from minimum to maximum from the complete table and i.e. between 1st January 2018 till 30-december-2021

  2. Dates[Date]>MAX(Dates[Date]) -30 -> "Takes Total Sales from the current row in table minus 30 days". For example if the DAX calculation is on 30th January 2018 then it considers all the total sales from 1st January 2018 till 30th January 2018

  3. Then why do we need to mention another filter Dates[Date] <= MAX(Dates[Date] )?

Thanks in advance for your time

Regards Sumit Malik

1
filtermaxdaxmeasure
This sounds more like a question to understand the formula. You can go simply google it and find a documentation or a video which can explain it better. – Gangula
Hi Gangula, Seymour answered my query. I hope you understood my query, I have tried to explain my understanding of formula but may have got confusing – Sumit Malik

1 Answers

0
votes

Sumit your main concern seem to be Point (3)

why do we need to mention another filter Dates[Date] <= MAX(Dates[Date] )?

Your doubt is correct, if the data is clean, you do not need to define that upper-bound filter because theoretically considering sales from 30 days ago, there should not be sales after today.
Unfortunately, often data is dirty and there might be Sales in the future. Therefore, defining an upper-bound is a best-practice to avoid this kind of dirty data issues. Remember that in software engineering you program thinking the worst-case scenario, therefore, defining an upper bound does not harm :)