Running total and reference line

[Lewdis_]

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. 

[Lewdis_ · Accepted solution]

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])

    )

[Anonymous]

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

[Lewdis_]

I have tried to use a field for running total and it works fine in the last graph where I use a date field

But if I want to show both CY and PY next to each other using Year-Month field it doesnt work

[Kedar_Pande]

@Lewdis_ 

Create measures for: CY Running Total, PY Running Total, Budget, Forecast.

Select a Line Chart and add your Date field to the Axis.
Add CY Running Total and PY Running Total to the Values.

In the Analytics pane, add Constant Lines for both Budget and Forecast.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Lewdis_]

I have a measure for YTD running total in general

Running YTD Sales = IF([ShowValueForSales]=TRUE(),TOTALYTD([Total sales],DimDate[Date]))

This one gives me the graph below but I cant use that one when i want to see all three year Jan-Dec

Then i am not sure how to make the budget one reference line on the top 

[gaurav-lakhotia]

Hi @Lewdis_,

Could you provide more information on the challenge you're facing?

You can simply use a line chart.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉

[Lewdis_]

I have started with a line chart showing CY, PY and Budget sales per month. But I dont know how to change them to be running.

Second is that i want the budget to be a total reference line on the top instead.