DAX calculate column cumulative total for the last 12 month

[nirrobi]

Hi,

I found a way to create calculate column for cumulative total for every account but I need to filter it for only the last 12 month - 

How can I add filter on date to check only the last 12 months for every line?

Many thanks!,

Nir

Cumulative MRR just for test =

CALCULATE(

   SUM( FactAccount[MRRgroup] ) ,

   ALLEXCEPT( FactAccount , FactAccount[AccountId] ) ,

  FactAccount[Index] <= EARLIER( FactAccount[Index] )

)

[Ashish_Mathur]

Hi,

Share the raw data, explain the business context and show the expected result.

[Fowmy · Accepted solution]

@nirrobi 

Hope you have a column named Date, if not rename the column name:

Cumulative MRR just for test =
CALCULATE(
   SUM( FactAccount[MRRgroup] ) ,
   ALLEXCEPT( FactAccount , FactAccount[AccountId] ) ,
   FactAccount[Index] <= EARLIER( FactAccount[Index] ),
   FILTER( FactAccount, 
     FactAccount[Date] >= EDATE(EARLIER(FactAccount[Date]),-12) &&  
     FactAccount[Date] <= EARLIER(FactAccount[Date])  ) 
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[nirrobi]

hi,

it seems that the dax function work

but

it's looks like it super uneffiecient - as samll table - 10 K rows - take few minutes to calcualte 

many thanks for your help!