cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nj17
Helper III
Helper III

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

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.

 

nj17_0-1669551321591.png

tagging for help @Jihwan_Kim  @amitchandak 

Thanks

nj

4 REPLIES 4
Mikelytics
Resident Rockstar
Resident Rockstar

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

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

nj17_0-1669569794204.png

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

nj17_1-1669569951335.png

 

 

thanks

nj

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @Mikelytics 

this is the calendar table i created

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

nj17_0-1669610443816.png

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
nj17_1-1669610566773.png

Data model

nj17_2-1669610596244.png

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.