Creating a calculated column for Prior Year - not working at drill down level of matrix

[Anonymous]

Hi, I have been on about 15 pages of similar topics and tried many formulas. They are either not working (give null values), or they do correctly give a prior year value, but it only works for the whole dataset and not the drill down levels.

Right now I am simply trying to get a column that shows a count of variable "PIDM" for the PRIOR year. This needs to be drilled down to various levels in a matrix as shown below in the screenshot. It should match the "Count of PIDM" column for the year to the left.

The closest I have is this formula: 

Prior_Yr8 = 
CALCULATE(COUNT('1_Enr'[PIDM]), ALL('1_Enr'), SAMEPERIODLASTYEAR('1_Enr'[YEAR]))

YEAR is in date format. When I put the column calculated above ("Prior_Yr8") in my matrix, I'm expecting it to show the same "Count of PIDM" as in the PRIOR year to the left.

It seems to be showing the Prior Year's value, but it's summed for the whole dataset, not drilled down to the dept level as it should be, as the Counts of PIDM are.

Can anyone help with my formula?

[v-yalanwu-msft]

Hi, @Anonymous ;

Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could try modify dax as follows:

Prior_Yr8 = CALCULATE(COUNT('1_Enr'[PIDM]), ALLEXCEPT('1_Enr','1_Enr'[Term.1]),SAMEPERIODLASTYEAR('1_Enr'[YEAR]))

If is not correct , can you share your PBIX after removing sensitive information?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Can you please create a date table, Mark it as date table and check

Prior_Yr8 = 
CALCULATE(COUNT('1_Enr'[PIDM]), ALL('1_Enr'), SAMEPERIODLASTYEAR('Date'[Date]))


Prior_Yr8 = 
CALCULATE(COUNT('1_Enr'[PIDM]), ALL('1_Enr'), dateadd('Date'[Date],-1, year))

Last year
CALCULATE(COUNT('1_Enr'[PIDM]), ALL('1_Enr'), filter(All(year), Year[Year] = Max(Year[Year])))

[Anonymous]

Thank you for this, @amitchandak, I appreciate it.

I do have a separate date table set up, and I marked it as a date table. It's connected to the 'date' field in my main dataset as shown in the screenshot below.

I tried all three of the measures you suggested. The first two (titled PriorYr_9 and PriorYr_10) resulted in a value of 89,060 in every field, which is the total number of rows in the dataset, all years combined. I'm guessing this is because of the "ALL" statement. See below:

The last suggested measure threw an error: