cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

hi @Pmorg73 ,

Just redo your series tables to:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

4 REPLIES 4
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

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Patron

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?

Super User

hi @Pmorg73 ,

Just redo your series tables to:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Patron

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.