cancel
Showing results for
Did you mean:  Helper I

## Sales running total for specific years

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 1 ACCEPTED SOLUTION  Community Support

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.

7 REPLIES 7  Community Support

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.  Helper I

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  Helper I

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

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.  Helper I

Thanks @edoardo_mussi, what if I want to show running totals by specific year, rather than the previous year?  Resolver I
`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).  Helper I

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.   