Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.