Wanting to do a date count up to last calendar of the previous month

[EmilyM2019]

Hello all,

I am building a report to give organisational data to senior stakeholders. What I need in the report is a date count between a due date and the reporting date which is always the last calendar day of the previous month.

This is the DAX that I am currently using which is based on a criteria within my final PDR rating column. This works for the reporting date of 31st Jan 21.

Count of days since due date = IF('WFP data'[Final PDR rating]="Outstanding",DATEDIFF('WFP data'[PDR due date],"31/01/2021",DAY),BLANK())

However, the obvious issue is that I would have to go into the formula and change the reporting date every month.

Does anyone know of any way of getting this to automatiocally count the calendar days from the PDR due date and the last calendar day of the previous month?

Any help would be appreciated.

Regards,

Emily.

[mahoneypat]

You can use EOMONTH(TODAY(), -1) to get the last date of the previous month dynamically.

Pat

[amitchandak · Accepted solution]

@EmilyM2019 , Try like

Count of days since due date = IF('WFP data'[Final PDR rating]="Outstanding",DATEDIFF('WFP data'[PDR due date],eomonth(today(),-1),DAY),BLANK())

[EmilyM2019]

Great, thank you.