Forum Discussion
Solved
Runing total of last 365 days in pivot table
Hello people, I'm looking for a solution to add to the Pivot Table RUNNING TOTALS of sales over the LAST 365 DAYS. For example 1st April 2021 = SUM Sales 02.04.2020 and 01.04.2021. Simple file ex...
- I found the solution already, in the Measure based on this DAX:
Sales Moving Annual Total =
CALCULATE (
[Sales Amount],
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-1,
YEAR
)
)
Thanks Riny, in principle I need this data, but in the pivot table/graph. Real data table is external with more than 100k rows. Can't go this way...
I found the solution already, in the Measure based on this DAX:
Sales Moving Annual Total =
CALCULATE (
[Sales Amount],
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-1,
YEAR
)
)
Sales Moving Annual Total =
CALCULATE (
[Sales Amount],
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-1,
YEAR
)
)
As variant
Running 365 Total:= VAR maxDate = MAX ( Sales[Date] ) VAR minDate = maxDate - 365 + 1 RETURN CALCULATE ( [Sales Total], ALLEXCEPT ( Sales, Sales[Date] ), Sales[Date] >= minDate && Sales[Date] <= maxDate )if build on the same table and without time intelligence.
- Thank you Sergei!
