Total per Month and Cumulative total in tabular format

[Anonymous]

Hi,

I am in the process of creating a table like this. Is it possible? I have a few thousand rows and I want to produce data on a per month and cumulative till date basis. Also, the status column has to be created manually and linked to the other columns. I am currently thinking of DAX and have no idea how to proceed. Any help will be much appriciated. Cheers, AA

[V-lianl-msft]

Hi @Anonymous ,

You can try what @amitchandak  said before.

But then comes the issue of reporting performance. If the dataset is large, it is recommended that you create a table and create a relationship between two tables.

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

[amitchandak]

You might need a table like this

New Table=
union(
summarize (Table,Table[Month],"Status","Contract Package issued","Month",[Month Value CPI],"Till date",[Till Dare Value CPI]),
summarize (Table,Table[Month],"Status","Contact Executed","Month",[Month Value CE],"Till date",[Till Dare Value CE])
)

For Till date you can use time intelligence and date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

You can also explore "Show on Rows" in the matrix

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/