Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Pmorg73
Post Patron
Post Patron

consolidate calculated values for cumulative chart

Hi everyone.

As part of a Covid response I have built a scenario/forecast for my bosses that allows them to alter each months costs for the running of the company. So I have 12 slicers that we enter a value in to, the measure returns the MAX value of the slicer and then this results in 12 values, one for for each month of the coming year. But these are then not related with any time intelligence.

I have a sweet total, as it is simply item1 + item2 + item3 etc for the 12 values, but they do not use my calendar to calulate, so they are "islands" of individual numbers.

 

Is there a way to consolidate the 12 individual values in to a table? I can not think of anything that does this. I can then relate the table to my calendar and do a cumulative sum to plot against my forecast income. (the income calc has time intelligence as it is a percentage of the previous years income)

 

Due to it being sensitive info I have not shared any images sorry.

1 ACCEPTED SOLUTION

hi @Pmorg73 ,

 

Just redo your series tables to:

Cost April = ADDCOLUMNS(GENERATESERIES(100; 500; 1);"Date";Date(2020;4;30))

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @Pmorg73 ,

 

I assume you have a date table, so I would try to add a date to each of the items, 1, 2, 3...12 with the end of the month for each one if you make this connected to your date table this will give you the cumulative value for. Just be sure to have a single date on each table (assuming is a whatiftable with a single date in all rows.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for responding

 

i have a date table yes.

 

The step I don't know how to do is how to assign a date to my measure. It is a simply number entry in the slicers

 

DAX used in measure: (there is a measure for each month considered in next year)

 

Costs April = GENERATESERIES(100,500,1)
new measure
Red costs April = max('Costs April'[cost apr])
new measure
Forecast Costs April 2020 = CALCULATE(1000 * 'Costs April'[Red costs April], '00_calendar'[Year]=2020, '00_calendar'[month number]=4)
 
so the user enters a value in the slicer, say 200. to equal 200k. I then do a measure to return the MAX of the entered slicer 
 
In the DAX above I multiply by 1000 to get the full number and then I tried to give it date info in red, but this does not work for me. In my original build I did it as a %ge of the previous years costs which worked but was a horrid user interface, and the month = 4 is a hangup from that. It does nothing here. Image attached of my report. The horizontal yellow line is the one I want to fix.
 
Any suggestions?
 
date issue.jpg
 
 

 

hi @Pmorg73 ,

 

Just redo your series tables to:

Cost April = ADDCOLUMNS(GENERATESERIES(100; 500; 1);"Date";Date(2020;4;30))

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



that looks really interesting and logical. Will give it a crack this afternoon. thanks a lot

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.