Count Dynamic Overdue Flag

[Michie_24]

Hi, 

Here is my example data. I would like to count the number of overdues for each months.

For example, Request C and D is "Not Overdue" if it is based on the month of February, but it will be "Overdue" when it is on March. I would like to have a graph that shows the accumulated number of Overdues for each month. Any leads would be helpful. 

[adudani · Accepted solution]

Hi @Michie_24

steps taken:

1. Created a conditional column in power query to check if Today is greater than the Target Date. Today will dynamically update based on your system locale.

2. If yes, overdue else not.

3. Note: Change the Today parameter in the conditional statement if any other date is required for this comparison.

CODE:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMjJR0lRyAG8gxNYQIgjgmM45yTX5yaohSrA9RlhKTLCaTQSN/IAq7LCMkIVF1I4ui6QAimygwm6IJVVSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submitted Date" = _t, #"Request ID" = _t, #"Target Date" = _t, #"Archived Date" = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Submitted Date", type date}, {"Request ID", type text}, {"Target Date", type date}, {"Archived Date", type date}, {"Status", type text}}),
OverdueFlag = Table.AddColumn(#"Changed Type", "Overdue Flag", each if Date.From(DateTime.LocalNow()) > [Target Date] then "Overdue" else "Not Overdue")
in
OverdueFlag

Furthermore, load this data into the report view.

4. Create the following measure:

Overdue count = CALCULATE( COUNT('Table (2)'[Overdue Flag]), 'Table (2)'[Overdue Flag] = "Overdue").

Please accept this solution if the query is resolved.