Forum Discussion
Count Dynamic Overdue Flag
Hi Anonymous
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
OverdueFlagFurthermore, 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.
Hi Anonymous
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: