topic Re: Measure to calculate current and previous year cumulative sales on monthly basis in DAX Commands and Tips

Measure to calculate current and previous year cumulative sales on monthly basis

gabcass — Tue, 14 Jan 2025 18:37:19 GMT

Hi,

I am trying to calculate the cumulative sales for the current year and also the previous year, with the ability to filter the data by year through a slicer.

I'm struggling because when the slicer is set for 2025, I can't display the previous year's sales for all months.

The following measure is not working:

Cumulative Sales Previous Year = CALCULATE(TOTALYTD([Sales], DATEADD(DimDate[Date], -365, DAY)))

My data model has a date table connected to a sales table via the date column.

How can I display the previous year's cumulative sales for all months when the slicer is filtering for the current year?

Thanks in advance!

Re: Measure to calculate current and previous year cumulative sales on monthly basis

lbendlin — Tue, 14 Jan 2025 22:07:54 GMT

You need to add another filter.

Cumulative Sales Previous Year = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( DimDate[Date] ), DimDate[Date] <= EDATE ( TODAY (), -12 ) )

Re: Measure to calculate current and previous year cumulative sales on monthly basis

govind_021 — Wed, 15 Jan 2025 04:35:10 GMT

Hi gabcass
You can create two separeate measures
Cumulative Sales Current Year =
TOTALYTD(
[Sales],
DimDate[Date]
)

Cumulative Sales Previous Year =
CALCULATE(
TOTALYTD(
[Sales],
DimDate[Date]
),
SAMEPERIODLASTYEAR(DimDate[Date])
)

Use both in the visual

Re: Measure to calculate current and previous year cumulative sales on monthly basis

Anonymous — Wed, 15 Jan 2025 06:20:24 GMT

Hi gabcass ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Measure to calculate current and previous year cumulative sales on monthly basis

GabeCass — Wed, 15 Jan 2025 11:35:08 GMT

Hi govind_021, thanks for your help.

The issue is that I have a slicer for year. The measure you suggested works well if I select previous years in my slicer:

However if I select 2025 the visual displays data for January only:

and this is OK for the current year, since we are in January, but actually I'd like the visual to display the previous year sales across all months, even if I'm slicing it for 2025.

Do you have any idea about how can I solve this?

Regards!

Re: Measure to calculate current and previous year cumulative sales on monthly basis

GabeCass — Wed, 15 Jan 2025 11:46:05 GMT

Hi lbendlin, thanks for your help.

The measure you suggested gives returns the cumulative sales in the previous year in this exact date.

My goal here is to display the current and previous year cumulative sales across all months, regardless of the year I'm slicing, like the image below:

But for 2025 as well.

Any clue on how can I achieve this?

Thanks!

Re: Measure to calculate current and previous year cumulative sales on monthly basis

sjoerdvn — Wed, 15 Jan 2025 16:54:36 GMT

Hi there,

The issue might not be the DAX, as the previous answers look OK. Are you sure that in your report context you actually have all dates for 2025? It might be that your date dimension does not have dates beyond today or that there is another filter active that filters out dates beyond today. If that's the case then that would cause SAMEPERIODLASTYEAR and comparable solutions to not work.
You can test that by creating dummy measure that returns a fixed value and adding that to the visual. It should return that value for each month.

Re: Measure to calculate current and previous year cumulative sales on monthly basis

GabeCass — Wed, 15 Jan 2025 17:02:40 GMT

Hi sjoerdvn

Actually I don't have all dates for 2025 beyond today in my date dimention. I'll include them and test if the recommended measures work. Thanks.

Re: Measure to calculate current and previous year cumulative sales on monthly basis

lbendlin — Wed, 15 Jan 2025 20:50:49 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Measure to calculate current and previous year cumulative sales on monthly basis

Anonymous — Tue, 21 Jan 2025 07:39:45 GMT

Hi gabcass ,

Any update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Measure to calculate current and previous year cumulative sales on monthly basis

GabeCass — Tue, 21 Jan 2025 12:18:16 GMT

DAX (CALENDAR(DATE(2023,01,01),DATE(2025,12,31))

The table is marked as as date table:

Dim Date

Date	Month	Year	DateWithSales
01/01/2023	Jan	2023	True
02/01/2023	Jan	2023	True
...	...	...
31/12/2025	Jan	2025	False

FactSales

OrderDate	ProductKey	Qty	UnitValue	TotalSales
10/01/2023	700187	300	5.75	1,725.00
...	...	...	...	...
20/01/2025	400402	2	1,268.00	2537.50

There is a one-to-many relationship between DimDate[Date] and FactSales[OrderDate]

In the Report View, there is a dropdown slicer for Date[Year] set for single selection:

The Goal:

I want to project two line charts:

One displaying current year and previous year monthly sales;

One displaying current year and previous year cumulative monthly sales.

Additional Details:

I am referring as the current year as the year set in the slicer, therefore according to the year selected I expect the charts to be updated automatically.

When slicing 2025, I don't want to display current year's sales for further months later to today's date as $0, having the line related to current year's sales displayed up to today's date month.

Measures Used:

Total Sales (measure used to compute current year sales and to calculate the other measures) =

VAR _Sales = CALCULATE(SUM('FactSales'[TotalSales]))

RETURN

IF(_Sales=0,0,_Sales)

Previous Year Sales =

CALCULATE([Total Sales],DATEADD(DimDate[Date],-1,YEAR))

Cumulative Sales =

CALCULATE(TOTALYTD(

[TotalSales],

DimDate[Date],DimDate[Date]<=TODAY()),

CALCULATETABLE(DATESYTD ('DimDate'[Date]),'DimDate'[DateWithSales]= TRUE))

Previous Year Cumulative Sales =

CALCULATE(

TOTALYTD(

[Total Sales],

DimDate[Date]

SAMEPERIODLASTYEAR(DimDate[Date])

)

Considering the scenario above, I've almost completed my goal, with the exception that in both charts I'm still displaying current year's monthly sales as zero for months later to today's date.

Do you have any work around for this?

P.S.: I'm not being able to select accept any answer as a solution. Could you explain me how can I do that?

Thanks in advance.