wrong % variation measure

[powerbricco]

Dear All,

I have the following measures:

1) [IMP. RIGA TOT.]  =  SUM(Query1[IMP_RIGA])

2) [Total Sales_PY_Sameday]  =  CALCULATE([IMP. RIGA TOT], DATEADD(CalendarTable[Date], -364, DAY))

Based on these two measure I created a third measure which calculates the variation % between the measures:

3) [Total Sales_PY% PY] = 

IF (ISBLANK( DIVIDE (

    ( [IMP. RIGA TOT] - [Total Sales_PY_Sameday] ),

    [Total Sales_PY_Sameday]

)), 0, DIVIDE (

    ( [IMP. RIGA TOT] - [Total Sales_PY_Sameday] ),

    [Total Sales_PY_Sameday]))

If I compare, for example, january 2024 with january 2023 this measure works fine, BUT if i compare the current month january 2025 with january 2024 it shows a wrong value beacuse it compares 30 days of january 2025 with 31 days of january 2024.

What should I add to the measure so that, when it compares a past month with the current month, it shows the variation between the current days? (For example, if today were the 7th of January 2025, it would show the variation between the first 7 days of January 2025 with the first seven days of January 2024, and not all the 31 days of January 2024?

Hope you can help me 😞

Lorenzo

[MattiaFratello]

Hi @powerbricco, have you tried to use SAMEPERIODLASTYEAR Dax Function? You may want to use MTD as well

[danextian · Accepted solution]

hi @powerbricco 

Instead of using DATEADD, why not use SAMEPERIODLASTYEAR?

Note:  Dates table must be marked as such.

[bhanu_gautam]

@powerbricco  You need to adjust your measure to account for the number of days that have passed in the current month. 

Create a measure to calculate the total sales up to the current day in the current month:

DAX
[IMP. RIGA TOT. YTD] =
CALCULATE(
[IMP. RIGA TOT.],
DATESBETWEEN(
CalendarTable[Date],
STARTOFMONTH(CalendarTable[Date]),
MAX(CalendarTable[Date])
)
)

Create a measure to calculate the total sales up to the same day in the previous year:

DAX
[Total Sales_PY_Sameday YTD] =
CALCULATE(
[IMP. RIGA TOT.],
DATESBETWEEN(
CalendarTable[Date],
SAMEPERIODLASTYEAR(STARTOFMONTH(CalendarTable[Date])),
SAMEPERIODLASTYEAR(MAX(CalendarTable[Date]))
)
)

Adjust your variation % measure to use these new measures:

DAX
[Total Sales_PY% PY] =
IF (
ISBLANK(DIVIDE(
([IMP. RIGA TOT. YTD] - [Total Sales_PY_Sameday YTD]),
[Total Sales_PY_Sameday YTD]
)),
0,
DIVIDE(
([IMP. RIGA TOT. YTD] - [Total Sales_PY_Sameday YTD]),
[Total Sales_PY_Sameday YTD]
)
)