Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I am trying to create a graph of cumulative cost over time based on what project is selected by the user on the dashboard.
Project | Date | Cost |
A | 20/01/2020 | 10.00 |
B | 20/01/2020 | 20.00 |
A | 20/01/2020 | 30.00 |
B | 21/01/2020 | 10.00 |
A | 21/01/2020 | 20.00 |
B | 22/01/2020 | 20.00 |
A | 23/01/2020 | 10.00 |
B | 23/01/2020 | 20.00 |
A | 23/01/2020 | 20.00 |
I have created this dummy data for show.
I have been trying to use DAX to created a cumulative cost column but the problems Ive been having are:
Solved! Go to Solution.
Hi @TimPowerBI ,
The solution will depend on how your data model is setup and can be achieved by using either a column or a measure.
Sol # 1: using a separate Date table.
Cumulative Sum Measure =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER ( ALL ( 'Dates'[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) )
)
Sol # 2 : no separate Dates table.
Cumulative Sum Measure =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER ( ALL ( 'Fact'[Date] ), 'Fact'[Date] <= MAX ( 'Fact'[Date] ) )
)
Sol # 3: as a calculated column.
Cumulative Sum Column =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER (
ALL ( 'Fact' ),
'Fact'[Date] <= EARLIER ( 'Fact'[Date] )
&& 'Fact'[Category] = EARLIER ( 'Fact'[Category] )
)
)
Proud to be a Super User!
Hi,
Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of your Calendar Table. To your visual/filter, drag the Project column from the Data Table and select any one project. Write these measures
Total cost = SUM(Data[Cost])
Total cost till date = Calculate([Total cost],datesbetween(Calendar[Date],minx(all(Calendar[Date]),Calendar([Date])),min(calendar[Date])))
Hope this helps.
Hi @TimPowerBI ,
The solution will depend on how your data model is setup and can be achieved by using either a column or a measure.
Sol # 1: using a separate Date table.
Cumulative Sum Measure =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER ( ALL ( 'Dates'[Date] ), 'Dates'[Date] <= MAX ( 'Dates'[Date] ) )
)
Sol # 2 : no separate Dates table.
Cumulative Sum Measure =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER ( ALL ( 'Fact'[Date] ), 'Fact'[Date] <= MAX ( 'Fact'[Date] ) )
)
Sol # 3: as a calculated column.
Cumulative Sum Column =
CALCULATE (
SUM ( 'Fact'[Column] ),
FILTER (
ALL ( 'Fact' ),
'Fact'[Date] <= EARLIER ( 'Fact'[Date] )
&& 'Fact'[Category] = EARLIER ( 'Fact'[Category] )
)
)
Proud to be a Super User!
Instead of a calculated column, this is better done with a measure like the one below. It will work in your visual whether Project is in it or not. For example, make a matrix with date on the rows and project on the column, with this measure in the values.
Cumulative Cost =
VAR __maxdate =
MAX ( Cost[Date] )
RETURN
CALCULATE (
SUM ( Cost[Cost] ),
ALLSELECTED ( Cost ),
VALUES ( Cost[Project] ),
Cost[Date] <= __maxdate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |