Last Month End Date formula in my calculation

[salleninsd]

Hello,

I am trying to set a calculation that returns a count on the last day of the month, from the prior month.

This is my formula that works for last month, and I want to replace "2018-09-30" with a formula so I don't have to change it every month.  I can't seem to get the syntax right:

Count({ $<  [Census_Daily.CensusDate]={"2018-09-30"}>}  [Census_Daily.CensusMRN])

I only have Census_Daily.CensusDate as a date field in my table, and I cannot connect that to my master calendar, because it is already being used by another date dimension.

I appreciate any suggestions you can send my way

[dplr-rn · Accepted solution]

You mentioned you tried a few things so would help if you share a sample file.

What i use usually is below

Define variable with your date logic (below is assuming you want last day of previous month)

vLastCensusDate

=Date(monthend(addmonths(today(),-1)),'YYYY-MM-DD')

use variable in set analysis like below

Count({ $<  [Census_Daily.CensusDate]={"$(=vLastCensusDate)"}>}  [Census_Daily.CensusMRN])

[salleninsd]

Thank you, Dilip!!!  This appears to have worked for me.

I appreciate your help!

[dplr-rn]

no problems