The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
+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
Solved! Go to 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.
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
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.
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 )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |