Forum Discussion

powerbricco's avatar
powerbricco
Advocate I
1 year ago
Solved

wrong % variation measure

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))   Ba...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    hi powerbricco 

    Instead of using DATEADD, why not use SAMEPERIODLASTYEAR?

     

     

    Note:  Dates table must be marked as such.

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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