cancel
Showing results for
Did you mean:
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
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```

3 REPLIES 3
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```

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.

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

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

Proud to be a Super User!

Helper I

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.

Announcements