Dax function on cumulative total

[Anonymous]

Hi I am tryting to build same caliculated filed  in power bi report conversioan (whcih is running on tableau).

Here is tableau syntax:

str(round(SUM([ Amount USD ])/1000,0)) + "K" +
(if ZN(LOOKUP(SUM([ Amount USD ]), -1)) = 0 then ""
ELSE " (" + STR(ZN(ROUND((ZN(SUM([ Amount USD ])) - LOOKUP(ZN(SUM([ Amount USD ])), -1))/1000,0))) + "K)" end)

It need to show the cumilative sum on month wsie & and need to show the diff in ( )

Ex: 

Aug 2020     Sep 2020  Oct 2020     Nov 2020       Dec 2020

100$            90$(-10)      80$(-10)   70$(-10)    60$(-10)

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

In Power BI you can use this month vs last month and diff like this date table and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

hi @amitchandak,

Thank you for your help,

I request help in English please .

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You need a date dimension table and refer to this sample .pbix

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