Percentage difference to month previous year

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

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.

[Anonymous]

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.