Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need some assistance to help me troubleshoot getting a running total for each date with a number. All the videos I've watched are CALCULATE [Total Sales] by creating a Total Sales measure. I am not using sales data. I am looking to add each number for each date in date order. I've tried CALCULATE with MAX [date] DAX codes, using VAR, I even tried Quick Measures and I just got the sum of all numbers repeated for each date. Do I need to create a measure to get the total of each number in the table column that contains 1 number per date? How can I just add JAN 1, 2022 = 10, + JAN 2, 2022 =5 then (10+5) = 15 + JAN 3, 2022 = 3 then (15+3) = 18 to create an increasing final number to DEC 31, 2024? Eventually, this will go into a line chart so it will be one ascending line from bottom to top.
I'm so confused! Can someone please thoroughly explain what I need to do or what I am doing wrong? Much appreciated!
Solved! Go to Solution.
Hi @moe10134
Are you using a date table? If so...
Running Total - w/DateTable =
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Start = DATE( 2022 , 1, 1)
VAR _End = MIN( DATE( 2024, 12, 31), _Curr )
VAR _RT =
CALCULATE(
SUM( 'DataTable'[Amount] ),
ALL( 'Date'[Date] ),
'Date'[Date] >= _Start,
'Date'[Date] <= _End
)
RETURN
_RT
If not...
Running Total - No DateTable =
VAR _Curr = SELECTEDVALUE( 'DataTable'[Date] )
VAR _Start = DATE( 2022 , 1, 1)
VAR _End = MIN( DATE( 2024, 12, 31), _Curr )
VAR _RT =
CALCULATE(
SUM( 'DataTable'[Amount] ),
ALL( 'DataTable'[Date] ),
'DataTable'[Date] >= _Start,
'DataTable'[Date] <= _End
)
RETURN
_RT
Let me know if you have any questions.
Running total for each date with a number.pbix
Hi @moe10134
Are you using a date table? If so...
Running Total - w/DateTable =
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Start = DATE( 2022 , 1, 1)
VAR _End = MIN( DATE( 2024, 12, 31), _Curr )
VAR _RT =
CALCULATE(
SUM( 'DataTable'[Amount] ),
ALL( 'Date'[Date] ),
'Date'[Date] >= _Start,
'Date'[Date] <= _End
)
RETURN
_RT
If not...
Running Total - No DateTable =
VAR _Curr = SELECTEDVALUE( 'DataTable'[Date] )
VAR _Start = DATE( 2022 , 1, 1)
VAR _End = MIN( DATE( 2024, 12, 31), _Curr )
VAR _RT =
CALCULATE(
SUM( 'DataTable'[Amount] ),
ALL( 'DataTable'[Date] ),
'DataTable'[Date] >= _Start,
'DataTable'[Date] <= _End
)
RETURN
_RT
Let me know if you have any questions.
Running total for each date with a number.pbix
Thank you so much for the DAX code and for providing me with a Power BI file to see how all this code applies from a table perspective. It completed my visual perfectly. I've been spinning my brain over this for 2 weeks, all your help is greatly appreciated. Thank you very much! 😀
You're welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
52 | |
45 | |
15 | |
12 |