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.
Hi all,
I am attempting to use DAX measures to create a count of rows in the same period last year using Power BI Desktop. I have tried using:
Measure = COUNTROWS(FILTER(Incidents,SAMEPERIODLASTYEAR(Incidents[Date])))
But don't get the same counts shifted by period. Anyone know what I am doing wrong?
The table below shows the results I am getting with the measure, compared to the current counts.
The top row of 'Measure' should show 179 (i.e. the count for Qtr1 2014).
Any advice gratefully received,
Will
Solved! Go to Solution.
Agreed with Austin.
You need separate date table with continous date values to be able use time intellegence functions.
dont forget to mark it as DATE table.
Create relationship from Incidents[Date] table to Date[Date].
Then use Date[Date] inside SAMEPERIODLASTYEAR.
Hi @Anonymous,
can you provide an example dataset?
In my opinion if you just want to count the rows like in your screenshot you can do it with a simple COUNTROWS() without any filter.
If you need something like this you can do it like in my screenshot.
Is this what you want? Tell me if i misunderstand your request.
#I'M Not An Expert#
My gut instinct here is that you need to use a separate Date table
Agreed with Austin.
You need separate date table with continous date values to be able use time intellegence functions.
dont forget to mark it as DATE table.
Create relationship from Incidents[Date] table to Date[Date].
Then use Date[Date] inside SAMEPERIODLASTYEAR.
I think Austin and Nilesh are on the right track.
Essentially, I want to use time intelligence to identify the difference between counts of rows last quarter and the same quarter last year. As an intermediate step I am trying to return these two values, before calculating the difference. However, I also want to be able to filter by other columns using a slicer, applying this filter to the count last quarter and the same quarter last year. This is one of my stumbling blocks, as I am comparing rows from different time points.
I will have a go at trying to create using a related date table and get back to you if I have further problems.
Thanks everyone for your thoughts!
Will
Thanks guys, that has done it!
Thanks for the pointers, I am much more confident in using date/time intelligence functions now. I have noticed I could use -12 and MONTH in the measure in (3).
Will
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |