Hi Everyone,
I need help with one of my PBI reports.
For the report I need to harmonize the months and find the similar business days.
Last working day of a month is considered "day 0", the workday prior "day -1", the workday prior to that "day -2".
How can I write a DAX to create this countdown on a calendar. At the bottom is the query code. Wasn't sure how to add the PBI
It is supposed to look something like this:
| Date | Weekday | Business day | Workday |
| 10/1/2023 | 7 | 0 | |
| 10/2/2023 | 1 | 1 | -21 |
| 10/3/2023 | 2 | 1 | -20 |
| 10/4/2023 | 3 | 1 | -19 |
| 10/5/2023 | 4 | 1 | -18 |
| 10/6/2023 | 5 | 1 | -17 |
| 10/7/2023 | 6 | 0 | |
| 10/8/2023 | 7 | 0 | |
| 10/9/2023 | 1 | 1 | -16 |
| 10/10/2023 | 2 | 1 | -15 |
| 10/11/2023 | 3 | 1 | -14 |
| 10/12/2023 | 4 | 1 | -13 |
| 10/13/2023 | 5 | 1 | -12 |
| 10/14/2023 | 6 | 0 | |
| 10/15/2023 | 7 | 0 | |
| 10/16/2023 | 1 | 1 | -11 |
| 10/17/2023 | 2 | 1 | -10 |
| 10/18/2023 | 3 | 1 | -9 |
| 10/19/2023 | 4 | 1 | -8 |
| 10/20/2023 | 5 | 1 | -7 |
| 10/21/2023 | 6 | 0 | |
| 10/22/2023 | 7 | 0 | |
| 10/23/2023 | 1 | 1 | -6 |
| 10/24/2023 | 2 | 1 | -5 |
| 10/25/2023 | 3 | 1 | -4 |
| 10/26/2023 | 4 | 1 | -3 |
| 10/27/2023 | 5 | 1 | -2 |
| 10/28/2023 | 6 | 0 | |
| 10/29/2023 | 7 | 0 | |
| 10/30/2023 | 1 | 1 | -1 |
| 10/31/2023 | 2 | 1 | 0 |
let
StartDate = Date.StartOfMonth(Today),
EndDate = Date.EndOfMonth(Today),
Duration = Duration.Days(Duration.From(EndDate-StartDate))+61,
Today = DateTime.Date(DateTime.LocalNow()),
Dates = List.Dates(StartDate,Duration,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Month" = Table.AddColumn(#"Changed Type", "Month No", each Date.Month([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Day of Week", "Business Day", each if [Day of Week]>0 and [Day of Week]<6 then 1 else 0)
in
#"Added Custom"
