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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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