Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all
I'm relatively new to Power BI and am trying to get a running total that covers every day even if the day doesn't have a value to sum. The calculation is for number of hours.
Here is how it currently looks:
25/06/2022 doesn't have any hours, but instead of showing 0 in the Running Total column it should show 29,436.00 until 30/06/2022.
The date column is from my date/calendar table and is joined to a date column in the table that holds the hours.
Here is the dax (measure) for the Running Total column. Thanks in advance for any help.
Running Total =
CALCULATE(
SUM('Plan Export'[Baseline Duration (Hours)]),
FILTER(
ALL('Plan Export'),
('Plan Export'[Baseline Start (Rounded)]) <= MAX ('Plan Export'[Baseline Start (Rounded)])
)
)
I've also tried this (measure again)... my brain is a bit mushy from looking at it for too long so it quite likely doesn't make sense!
Running Total =
VAR BaseLineHours = SUM('Plan Export'[Baseline Duration (Hours)])
VAR RunningTotalCalc =
CALCULATE(
BaseLineHours,
FILTER(
ALL('Plan Export'),
('Plan Export'[Baseline Start (Rounded)]) <= MAX ('Plan Export'[Baseline Start (Rounded)])
)
)
Return RunningTotalCalc
Thanks in advance for any help.
Solved! Go to Solution.
Hello @mwrdm ,
You can try the following code:
Running Total =
CALCULATE (
SUM ( 'Plan Export'[Baseline Duration (Hours)] ),
FILTER (
ALL ( 'Plan Export' ),
( 'Plan Export'[Date] ) <= MAX ( 'Plan Export'[Date] )
)
)
However, it is advisable to create a date dimension table and use that.
Please let me know if this doesn't help.
Hi, @mwrdm
In case you have a date table e.g. 'Date'[Date], this part of the measure should be changed to:
*****
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date] ) )
*****
Please refer to my article https://allure-analytics.com/index.php/2022/05/24/time-intelligence-in-power-bi/
Proud to be a Super User!
Hello @mwrdm ,
You can try the following code:
Running Total =
CALCULATE (
SUM ( 'Plan Export'[Baseline Duration (Hours)] ),
FILTER (
ALL ( 'Plan Export' ),
( 'Plan Export'[Date] ) <= MAX ( 'Plan Export'[Date] )
)
)
However, it is advisable to create a date dimension table and use that.
Please let me know if this doesn't help.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |