cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karlanka
Helper I
Helper I

Percentage difference to month previous year

I have a table with visiting dates, each row represents a visit and there are mulitple visits every day. I want to find the difference in percentage for the number of visitors per month compared to the same month previous year. Example: there are 250 visitors (250 rows with dates spread uneven over the days in march -15) in march -15 and 500 visitors in march -16, then the difference would be 100 %. How could this be done with DAX?

1 ACCEPTED SOLUTION
karlanka
Helper I
Helper I

Stupid me didnt realize i could create a separate unique date only-column and create relationship to my tables with visits, while still using other columns from the visittable,  and then work out the dax from there.

 

 

Visits = COUNT('visitstable'[VisitDates])

Visits LY = CALCULATE([Visits]; PARALLELPERIOD('datetable'[Dates];-12;MONTH))

Visits difference = [Visits]/[Visits LY] - 1

 

View solution in original post

3 REPLIES 3
karlanka
Helper I
Helper I

Stupid me didnt realize i could create a separate unique date only-column and create relationship to my tables with visits, while still using other columns from the visittable,  and then work out the dax from there.

 

 

Visits = COUNT('visitstable'[VisitDates])

Visits LY = CALCULATE([Visits]; PARALLELPERIOD('datetable'[Dates];-12;MONTH))

Visits difference = [Visits]/[Visits LY] - 1

 

View solution in original post

Greg_Deckler
Super User IV
Super User IV

So, taking a page from a Sales model that you should be able to translate, create a few measures:

 

Sales YTD = TOTALYTD(Sum('Sales'[Revenue]),'Date'[Date])

Sales LY YTD = CALCULATE ([Sales YTD], SAMEPERIODLASTYEAR ('Date'[Date]))

Sales Var = [Sales YTD]-[Sales LY YTD]

Sales Var % = [Sales Var] / [Sales LY YTD]

Something along those lines.


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

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I get:

 

Error Message:

MdxScript(Model) (1, 85) Calculation error in measure 'Table'[Visits LY YTD]: A date column containing duplicate dates was specified in the call to function 'SAMEPERIODLASTYEAR'. This is not supported.

 

The problem seems to be that im counting dates and there are multiple rows containing the same date.

 

Edit: I'm aware that one solution would be to create a separate date-table and count occurences for each day in the original table, but there are further dimensions in the original table i would like to use.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors