SOLVED

Runing total of last 365 days in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-3121287%22%20slang%3D%22en-US%22%3ERuning%20total%20of%20last%20365%20days%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121287%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20people%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20solution%20to%20add%20to%20the%20Pivot%20Table%20RUNNING%20TOTALS%20of%20sales%20over%20the%20LAST%20365%20DAYS.%20For%20example%201st%20April%202021%20%3D%20SUM%20Sales%2002.04.2020%20and%2001.04.2021.%20Simple%20file%20example%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Running_365.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345583i824A6EA21A878068%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Running_365.jpg%22%20alt%3D%22Running_365.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20advice%20and%20tips.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3121287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121342%22%20slang%3D%22en-US%22%3ERe%3A%20Runing%20total%20of%20last%20365%20days%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563911%22%20target%3D%22_blank%22%3E%40PavelDrg%3C%2FA%3E%26nbsp%3BNot%20sure%20but%20perhaps%20like%20in%20the%20attached%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121361%22%20slang%3D%22en-US%22%3ERe%3A%20Runing%20total%20of%20last%20365%20days%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121361%22%20slang%3D%22en-US%22%3EThanks%20Riny%2C%20in%20principle%20I%20need%20this%20data%2C%20but%20in%20the%20pivot%20table%2Fgraph.%20Real%20data%20table%20is%20external%20with%20more%20than%20100k%20rows.%20Can't%20go%20this%20way...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121556%22%20slang%3D%22en-US%22%3ERe%3A%20Runing%20total%20of%20last%20365%20days%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121556%22%20slang%3D%22en-US%22%3EI%20found%20the%20solution%20already%2C%20in%20the%20Measure%20based%20on%20this%20DAX%3A%3CBR%20%2F%3E%3CBR%20%2F%3ESales%20Moving%20Annual%20Total%20%3D%3CBR%20%2F%3ECALCULATE%20(%3CBR%20%2F%3E%5BSales%20Amount%5D%2C%3CBR%20%2F%3EDATESINPERIOD%20(%3CBR%20%2F%3E'Date'%5BDate%5D%2C%3CBR%20%2F%3EMAX%20(%20'Date'%5BDate%5D%20)%2C%3CBR%20%2F%3E-1%2C%3CBR%20%2F%3EYEAR%3CBR%20%2F%3E)%3CBR%20%2F%3E)%3C%2FLINGO-BODY%3E
Occasional Contributor

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 example attached.

 

Running_365.jpg

 

Thank you in advance for your advice and tips.

5 Replies

@PavelDrg Not sure but perhaps like in the attached file?

 

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...
best response confirmed by PavelDrg (Occasional Contributor)
Solution
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
)
)

@PavelDrg 

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!