Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |