Creating a running total measure

[tjlundquist1]

So I am trying to create a running total of the "AvgSpend/Month" (which I have displayed as a column in the table below), by Calander[Column 3](Month). I've tried a handful of measures and just can't get anythign to work as intended.

I have a "Calander" table that has a relationship with the current table that I call "Historical."

I intend on this running total meaure to be displayed right next to the AvgSpend/Month on the table below showing the spend at certain points in the year. (The AvgSpend is just an average of 5 years worth of data).

Any ideas?

[v-diye-msft]

Hi @tjlundquist1 ,

You are referring the MTD calcullation. there're TOTALMTD and DATESMTD for your option. 

Please see the official document: 

https://docs.microsoft.com/en-us/dax/totalmtd-function-dax

https://docs.microsoft.com/en-us/dax/datesmtd-function-dax 

And i'd like to suggest you reading this blog when calculating the running total but there no specific dates column :http://www.mssqltips.com/sqlservertip/4841/calculating-mtd-qtd-ytd-running-and-cumulative-total-in-p...

Best regards,

Dina Ye

[tjlundquist1]

Unfortunatly that didn't work for me, I've tried a few variations of that formula and it just doesn't seem to work.

@v-diye-msft 

[v-diye-msft]

Hi @tjlundquist1 ,

No need to the create the calendar table, you can use the month data in the fact table as column, also make sure the month is displaying by number rather than text. and then try your formula again. 

If you still can't make it, please share me your dummy pbix. (Upload to Onedrive/Sharepoint/dropbox, the share the link)

Best regards,
Dina Ye

[Anonymous]

Hi @tjlundquist1 

Following article will help you out.

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Thanks