Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |