Forum Discussion
Cumulative Column Values where every column represents a month and every line a measure
Hello PBI-firends,
I have a prefilled datatable for futue project management purposses.
Every line represents a measure that should save money.
Every column (at least those of interest) represents a month.
Every Value ist the saving of this month and this measure in thousand €.
Is there a way to cumulate the values column-wise from a start-date to an end-date?
What Format would the column-headers need for PBI to understand that "the time/date increases from left to right"?
in the end i want to have a dashboard, that can be filtered via the shown dropdown-menus and shows the cumulated column-vallues.
Thank you very much in advance,
David
Dave_Delfino , the screenshot is not very clear.
You need to unpivot the data.
Then you need take out month year from it
create date from month year and then create cumulative like example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))
5 Replies
- amitchandak
Super User
Dave_Delfino , the screenshot is not very clear.
You need to unpivot the data.
Then you need take out month year from it
create date from month year and then create cumulative like example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date]))) Hello Amit!
Thank you for your fast reply. I will go ahead and google the phrases you used to explain your solution.
Sorry for the not self-explanatory screenshots. i will come back to you as soon as i have solved my issue or are stuck again.
Greeting,
David
- amitchandak
Dave_Delfino , unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
https://www.youtube.com/watch?v=2HjkBtxSM0gTo separate out month year you need use Start, end and middle , as per need https://www.youtube.com/watch?v=vky4wPqm0O0
Create date from month year -https://www.youtube.com/watch?v=cJqgphIHXz8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=4
I have a similar video on DAX too
Hello again,
i have managed to un-pivot the needed data (Thank you for that hint!). But for now i have problem with cumulating the data in a measure.
Kosolidierung = Cosolidation (Name of table)
Einsparung = Savings (what i want to cumulate)
Datum = Date (well.., the date)do you have another hint for me on that regard?
best regards,
David
Please ignore my message. The answer you gave were completly sufficient to solve the problem.
My second problem (cumulation) has been resolved by formating the column to from text to decimal.
Again, thank you Amit.
