Create a table based on measured values

[plmiquelon]

+Hi,

I would like to see if it would be possible to use values obtained from measure to create a table

I want to create a table that has 2 column, one is date and second is cumulative cost. The table will have x rows, x being the total amount of day the project has (here its 21+43+67+224+30 = 385 days).

The date column starts on 08-NOV-2021, and adds a date for 385 days

The cumulative cost column goes as follow

for 21 days, add 34,65$ to the previous day

for 43 days, add 115,35$ to the previous day

for 67 days, add 185,61$ to the previous day,

for 224 days, add 437,51$ to the previous day

and for 30 days, add 262,40$ to the previous day.

This will serve as my planned project budget over the duration of the project. I would like to plot this against cumulative real cost that I have in another measure (that is no issue)

Is this something feasible within Power BI?

Thanks!

PL

[plmiquelon]

Hi,

Thanks for your help.

My problem with this approach is that the table you see above is obtained using filter that are locally applied to specific projects. I'm not sure that the data will be maintained when creating a table

Any toughts?

Thank you

[johnt75 · Accepted solution]

You can apply any filters you need by wrapping the whole thing in a CALCULATETABLE call. You can see exactly which filters were applied to your table visual by using Performance Analyzer to get the DAX code used to generate the query for the table.

[johnt75]

You need a date table for this approach to work, you can create your table using

New Table = GENERATE( SELECTCOLUMNS('Table', "Start date", 'Table'[start date], "Days", 'Table'[num days], "Cost", 'Table'[Cost]),
DATESBETWEEN( 'Date'[Date], [Start date], [Start date] + [Days] - 1))

Then create a relationship between the new table and your date table. You can then create either a measure or a calculated column to show the cumulative cost. A measure would look something like

Cumulative Cost =
var maxDate = MAX('Date'[Date])
return CALCULATE( SUM('New Table'[Cost]), REMOVEFILTERS('Date'), 'Date'[Date] <= maxDate )