Find the variance % of current and previous year sales based on selection of ytd,mtd and dates

[nj17]

Hi team,

How can i create a measure which shows % variance of sales of current year and previous year.

the variance should work with below slicer like Today,MTD,YTD and also with date slicer like Year,Month and Day.

below vPY is the variance in sales.I want this measure to be filtered by all the slicers.

tagging for help @Jihwan_Kim  @amitchandak 

Thanks

nj

[Mikelytics]

Hi @nj17 

Typically the solution would look like:

PY Delta % =

var var_CY_value = [your base measure]

var var_PY_Value=
   CALCULATE(
      [your base measure],
      DATEADD(Date_Table[Date Column],-1,MONTH)
   )

RETURN
DIVIDE(var_CY_value,var_PY_Value) - 1

Now it depends on your base measure as well as on your data model whether and how it works with the slicers and settings you mentioned.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[nj17]

Please find the link where i uploaded my file

https://www.dropbox.com/s/2lz9mbcdyq67rno/test.pbix?dl=0

When I select Year from slicer ,values are filtering

when I select below filter from mtd ytd slicer then no value appear for both the measures

thanks

nj

[Mikelytics]

Hi @nj17,

Due to security reasons in general I dont download files. I hope for your understanding.

Can you please provide a picture of the data model as well as the current measure definitions you use? Then it should be quickly solvable.

Best regards

Michael

[nj17]

Hi @Mikelytics 

this is the calendar table i created

Calendar = CALENDAR(DATE(2019,1,1),DATE(2022,12,31))

Slicer I created for MTD,YTD and Daily

MTD/YTD Selection =

 VAR TodayDate = TODAY()

VAR Daily = TODAY() - 1

 VAR YearStart = CALCULATE(STARTOFYEAR('FactSale'[transaction date]),YEAR('FactSale'[transaction date]) = YEAR(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH('FactSale'[transaction date]),YEAR('FactSale'[transaction date]) = YEAR(TodayDate),MONTH('FactSale'[transaction date]) = MONTH(TodayDate))

VAR Result =

UNION (

  ADDCOLUMNS (

        CALENDAR(Daily,TodayDate),

"Selection","Daily"

),

ADDCOLUMNS (

        CALENDAR(MonthStart,TodayDate),

"Selection","MTD"

),

ADDCOLUMNS (

        CALENDAR(YearStart,TodayDate),

"Selection","YTD"

)

)

RETURN

Result

FactSale have below values

Data model

Measures I created

Sales = sum(FactSale[Amount])

Sales Amount PY = CALCULATE(SUM('FactSale'[Amount]),SAMEPERIODLASTYEAR('Calendar'[Date]))

Sales Amount vPY = IFERROR(CALCULATE(IF([Sales Amount PY]=0,"-",  SUM('FactSale'[Amount]) -[Sales Amount PY])/[Sales Amount PY]),"-")