Forum Discussion
Solved
Running total and reference line
I am trying to build a running total for CY and PY for the month like the graph below. I would also like budget and forecast to be reference lines on the top.
solved it with your help and added a filter for current year. Then the following measures
Full year budget (one number) =CALCULATE([Total Budget],DATESINPERIOD(DimDate[Date],DATE(2024,1,01),1,YEAR))Running YTD last year =CALCULATE([Running YTD],SAMEPERIODLASTYEAR(DimDate[Date]))Running YTD =CALCULATE([Total sales],DATESYTD(DimDate[Date]))
Hi Lewdis_ ,
As Kedar_Pande said, you can do this by adding a constant line for the y-axis in the analysis window. Here is the example data
| Date | CY Sales | PY Sales | CY Budget |
| 1/1/2024 | 1000 | 2000 | 1000 |
| 2/1/2024 | 2000 | 4000 | 2000 |
| 3/1/2024 | 2500 | 5000 | 3000 |
| 4/1/2024 | 4500 | 4000 | 4000 |
| 5/1/2024 | 3000 | 1000 | 5000 |
| 6/1/2024 | 2000 | 2000 | 6000 |
| 7/1/2024 | 1000 | 3000 | 7000 |
| 8/1/2024 | 2000 | 2000 | 8000 |
| 9/1/2024 | 6000 | 1000 | 9000 |
| 10/1/2024 | 4000 | 4000 | 10000 |
| 11/1/2024 | 1200 | 3000 | 11000 |
| 12/1/2024 | 3500 | 1500 | 12000 |
Create columns
Running Total of PY Sales =
VAR _currentDate = 'Table'[Date]
RETURN
SUMX(
FILTER(
'Table',
'Table'[Date] <= _currentDate
),
'Table'[PY Sales]
)Running Total of CY Sales =
VAR _currentDate = 'Table'[Date]
RETURN
SUMX(
FILTER(
'Table',
'Table'[Date] <= _currentDate
),
'Table'[CY Sales]
)Running Total of CY Budget =
VAR _currentDate = 'Table'[Date]
RETURN
SUMX(
FILTER(
'Table',
'Table'[Date] <= _currentDate
),
'Table'[CY Budget]
)
Create measure
Sum of budget = SUM('Table'[CY Budget])
Create line chart and constant line
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly