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
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
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
Then why do we need to mention another filter Dates[Date] <= MAX(Dates[Date] )?
Thanks in advance for your time
Regards Sumit Malik
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 :)