Date Calculations - 15th of the month / Last Day of month - Last Year

[bigck2]

Hello Community,


In my data model I have calculated fields: occupancy, occupancy prior year, and year over year Var.


The formula for occupancy prior year is:

=CALCULATE( [Occ %] , SAMEPERIODLASTYEAR( DimDates[Date] ) )


This works fine but I want to be able to be able to apply a filter to do the analysis on the 15th of the month or the last day of the month.

I tried to do do this by adding a column to my DimDates table. I added a Day column, End of Month Column, and 15th Column. The End of Month Column and 15th column are true false a values, so I thought I could just apply these filters to the Pivot Table and limit this to the 15th or the last day of month.

However, I'm getting this error message when I try to apply the filter: 'SAMEPERIODLASTYEAR' only works with contiguous date selections.

Please let me know if you have any suggestions on how I might be able to work around this one.


Thanks!

Chris

 

[bigck2]

I was able to work around this by creating separate calculated fields for [Occ % - 15th] and [Occ % - EOM], where:

[Occ % - 15th] = CALCULATE( [Occ %], DimDates[15th] = TRUE )

[Occ % - EOM] = CALCULATE( [Occ %], DimDates[EOM] = TRUE )


From here I am able to create 2 more calculated fields:

[Occ % - 15th - Prior Year] = CALCULATE( [Occ % - 15th], SAMEPERIODLASTYEAR( DimDates[Date] ) )

[Occ % - EOM - Prior Year] = CALCULATE( [Occ % - EOM], SAMEPERIODLASTYEAR( DimDates[Date] ) )


Then finally two more Var fields:

[Occ % - 15th - YoY - Var] = [Occ % - 15th] - [Occ % - 15th - Prior Year]

[Occ % - EOM - YoY - Var] = [Occ % - EOM] - [Occ % - EOM- Prior Year]


I guess this works to accomplish my need of producing pivot table reports with these measures. I'm a little bit confused as to why this works, and my first attempt of just applying the Filter of 15th = True or EOM = True produced an error wit the SAMEPERIODLASTYEAR function.

I'm also wondering if what I've done is the most efficient work around?

Looking at the original data these calculated fields seems to be producing the result I am looking for.

Thanks,

Chris