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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.