Custom Column in PowerBI to calculate number of dates which are more than 3 months before today

[GeorgeR1]

I am using a calculated column in PowerBI to identify whethere a date is older than 3 months from today.

My table is like so:

Date

30 July 2022

30 July 2022

30 July 2022

15 October 2021

28 February 2022

29 June 2020

29 December 2021

I am currently doing this manually, by entering the day that is 3 months ago, however I want to automate this so no manual adjustment. I have written as follows:

Current Action Status =

IF(Actions[Action Due Date]<=date(2021,11,10),"Overdue by 3+ Months",

IF(Actions[Action Due Date]<today(),"Overdue",

IF(EOMONTH(Actions[Action Due Date],0)=EOMONTH(TODAY(),0),"Due This Month","Due Later"

)

)

)

I want to replace the date(2021,11,10) with something that calculates Today - 3 months.

[BA_Pete · Accepted solution]

Hi @GeorgeR1 ,

This is something I would do in Power Query if possible. This will save the end user having to materialise calculated columns in memory at runtime, and I also personally find it much simpler to write.

Something like this in a new custom column should do it:

let
  Date.Today = Date.From(DateTime.LocalNow())
in
if [Action Due Date] <= Date.AddMonths(Date.Today, -3) then "Overdue by 3+ Months"
else if [Action Due Date] < Date.Today then "Overdue"
else if Date.EndOfMonth([Action Due Date]) = Date.EndOfMonth(Date.Today) then "Due This Month"
else "Due Later"

Pete

[GeorgeR1]

Brilliant, thank you. This is exactly what I was looking for.