Dax for cumulative sum on Measure

[Anonymous]

Hi,

I need to get running total for the difference of Accounting Year and Last Year as seen below.

Accounting Year and Last Year are both measures. Accounting Year is dervied using the formula- Sum(A)  

Last Year is derived using the formula- CALCULATE((SUM(A)),SAMEPERIODLASTYEAR(CALENDAR(Accounting Date))

Accounting Date is used in the year and month chiclet slicer.

Previous Period is a calculated column with the formula- Previous Period = DATEADD('Previous Calendar'[Accounting date],-1,YEAR).

I need to have a cumulative sum on the Difference. I am aware of the dax formula for Cumulative Sum but since my Difference is a mesaure I am not able to perform Sum(Difference). My Calendar table consists of contigious dates.  I would appreciate any help.

Thanks,

Deeptha 

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[Anonymous · Accepted solution]

Hi Dale,

Thanks for your help and  time. I used YTD as suggested by you but it was accumulating for an year instead of accumulating for a month. Hence I used MTD and could achieve what was required. So I could say that your solution helped me crack the requirement.

[v-jiascu-msft]

@Anonymous,

Hi Deeptha,

According to my test, I would suggest function "TotalYTD".

Cumulate Current Year =
TOTALYTD ( SUM ( A ), 'Calendar'[Accounting Date] )

Cumulate Previous Year =
CALCULATE (
    TOTALYTD ( SUM ( A ), 'Calendar'[Accounting Date] ),
    SAMEPERIODLASTYEAR ( 'Calendar'[Accounting Date] )
)

Best Regards!

Dale

[TomMartens]

Hey, I assume you have a separate CalendarTable, if not you should consider to create one, almost everything around time related calculations is covered here:

http://www.daxpatterns.com/time-patterns/ 

You can create two measures

Running Current Year like this

Running Current Year = 
CALCULATE (
    SUM('yourtable'[Accounting Year]),
    FILTER (
        ALL ( 'Date'[CalendarDate] ),
        'Date'[CalendarDate] <= MAX ( 'Date'[CalendarDate] )
            && YEAR ( 'Date'[CalendarDate] ) = MAX ( 'Date'[CalendarYear] )
    )
)

and a running Previous Year like so

Running Previous Year = 
CALCULATE (
    SUM('yourtable'[Accounting Year]),
    FILTER (
        ALL ( 'Date'[CalendarDate] ),
        'Date'[CalendarDate] <= MAX ( 'Date'[CalendarDate] )
            && YEAR ( 'Date'[CalendarDate] ) = MAX ( 'Date'[CalendarYear] )-1
    )
)

Now you should be able to calculate the difference by just subtracting both measures