Sales running total for specific years

[DataScope06]

I would like to compare a specific year's running total of sales by month to another year's running totals by month.

For example, the running total of 2021 compared to 2022, with month on the x-axis.

My data structured is shown in the abbreviated table below. Underneath the table is a sketch of what I would like the chart look like.

Date	Sales
1/2/2021	500
2/1/2021	400
6/20/2021	700
1/2/2022	400
3/1/2022	600
4/1/2022	300

[Anonymous · Accepted solution]

Hi @DataScope06 ,

You could create a calendar and then create a measure to calculate the running total.

Calendar = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Year",YEAR([Date]),"MonthNumber",MONTH([Date]),"Month",FORMAT([Date],"MMMM"))

Select the Month column and sort by the MonthNumber column.

The relationship is as follows.

Create the measure.

Running Total = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Calendar'),[Year]=MAX('Calendar'[Year])&&[MonthNumber]<=MAX('Calendar'[MonthNumber])))

You can dowload my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataScope06]

This worked! Thank you. 

Also, part of my issues was due to discrepancy between fact and calendar table date formats. Specifically this was because my fact table date was in a datetime format. I found a blog post with details about the way this effects the relationship. Anyone having that issue can see the blog here: Relationship Issues With DateTime Data Types in Power BI — Havens Consulting

[DataScope06]

The thread linked below might have a solution posted by @amitchandak, however I am unable to download the workbook.

Solved: Running Total Chart over Multiple Years - Microsoft Power BI Community

[Anonymous]

For previous year:

CALCULATE(SUM(FactsTable[Sales]), SAMEPERIODLASTYEAR('Date'[DateKey]))

Then it's just a matter of using the sales column and applying a hidden filter so that it only shows data from the current year.

[DataScope06]

Thanks @Anonymous, what if I want to show running totals by specific year, rather than the previous year?

[Anonymous]

CALCULATE(
    SUM(Fatture[Importo Pagato]),
    DATEADD(
        DATESINPERIOD(
            'Date'[DateKey],
            "yourDesiredStartDate",
            nOfIntervals,
            interval
        ),
        nOfIntervals, //in multiples of 12 for months
        MONTH/YEAR
    )
)

 This should allow you to get any year you want.

You could also use FILTER(YEAR(DateKey)=yourDesiredYear).

[DataScope06]

I can't get your solution to work. Still searching for the solution to this issue.

Reminder the goal is to display the running total of sales for individual years on the same chart.