Forum Discussion
Dax function on cumulative total
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)
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.
3 Replies
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.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.
