Using DAX to create count of rows for same period last year

[Anonymous]

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

[Anonymous]

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#

[austinsense]

My gut instinct here is that you need to use a separate Date table

[Anonymous · Accepted 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. 

[Anonymous]

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

[Anonymous]

Thanks guys, that has done it!

1. I created a separate Date table of continuous dates using: Date = CALENDAR(MIN(Incidents[Date]),max(Incidents[Date]))
2. I then created a relationship between the Incidents[Date] field and the Date[Date] field.
3. I then created a measure in the Incidents table using: IncidentsLastYear = CALCULATE(COUNT('Incidents'[Index]),PARALLELPERIOD('Date'[Date], -4, QUARTER))
4. All my outputs were then as desired. I used the Date[Date] as the field for slicers and everything worked as planned.

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