Columns instead of Measures for the sum of the last 5 months

[Anonymous]

Hey everybody. 

I have created a calculated measure summarizing the Total AR for the last 5 months. The formula is bellow:

TOTAL AR (last 5 mts) = CALCULATE(SUM(VIEW_AR_MONTH[Total AR]), DATESINPERIOD(VIEW_AR_MONTH[Derived Date],LASTDATE(VIEW_AR_MONTH[Derived Date]),-5,MONTH))

This works like a charm = i see the summarization of the last 5 months and if I include the derived date in the table, I see the calculation for every previous month.

NOW. I need to do this as a calculated Column and not measure (for filtering reasons later on). 

The formula provided no longer works and it gives me only the current month numbers with no summarization at all. I know the columns and measures are different, but I couldn't solve this issue. 

Does anybody have an idea how to achieve this?

Cheerio,

Kris

[v-xicai]

Hi @Anonymous  ,
 
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards
Amy

[v-xicai]

Hi @Anonymous ,

I can't figure out how to create column to meet your demand currently, while you can try to create measures like DAX below. 

Period End = LASTDATE(VIEW_AR_MONTH[Derived Date])

Period Start= FIRSTDATE( DATESINPERIOD(VIEW_AR_MONTH[Derived Date], [Period End], -5, MONTH))

Sum Rolling 5 month = CALCULATE(SUM(VIEW_AR_MONTH[Total AR]),DATESBETWEEN ( VIEW_AR_MONTH[Derived Date], [Period Start], [Period End] ))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.