Using Power Query or Dax to create a custom date table for DoD, WoW, MoM view

[Anonymous]

Hi all,

I am converting a report from Excel to Power BI, and one of the requested formats is to have a table that shows the last five days, the last five weeks, and the three most recent months in the columns and the KPIs in the rows.

The resulting view is something like this:

Metric	8/24/2022	8/25/2022	8/26/2022	8/29/2022	8/30/2022	Jul Wk 5	Aug Wk 1	Aug Wk 2	Aug Wk 3	Aug Wk 4	Jun-22	Jul-22	Aug-22
KPI 1	3.86	3.42	5.11	4.54 - 4.80	4.40 - 4.71	3.57	4.00	3.81	4.09	4.04	3.93	3.92	3.97
KPI 2	49.4%	49.0%	58.5%	52.8%	56.9%	48.5%	50.1%	48.2%	52.4%	55.1%	56.1%	54.7%	53.5%

I can solve this by importing the following table that I created in excel, linking the second column to my date table, filtering in both directions, sorting the "Daily_Report_Name" column by the sort order column, and then creating a matrix with the first column of this table in the columns and the KPI measures as values:

Daily_Report_Name	Date_Range	Sort Order
8/24/2022	8/24/2022	1
8/25/2022	8/25/2022	2
8/26/2022	8/26/2022	3
8/29/2022	8/29/2022	4
8/30/2022	8/30/2022	5
Jul Wk 5	7/25/2022	6
Jul Wk 5	7/26/2022	6
Jul Wk 5	7/27/2022	6
Jul Wk 5	7/28/2022	6
Jul Wk 5	7/29/2022	6
Aug Wk 1	8/1/2022	7
Aug Wk 1	8/2/2022	7
Aug Wk 1	8/3/2022	7
Aug Wk 1	8/4/2022	7
Aug Wk 1	8/5/2022	7
Aug Wk 2	8/8/2022	8
Aug Wk 2	8/9/2022	8
Aug Wk 2	8/10/2022	8
Aug Wk 2	8/11/2022	8
Aug Wk 2	8/12/2022	8
Aug Wk 3	8/15/2022	9
Aug Wk 3	8/16/2022	9
Aug Wk 3	8/17/2022	9
Aug Wk 3	8/18/2022	9
Aug Wk 3	8/19/2022	9
Aug Wk 4	8/22/2022	10
Aug Wk 4	8/23/2022	10
Aug Wk 4	8/24/2022	10
Aug Wk 4	8/25/2022	10
Aug Wk 4	8/26/2022	10
Jun-22	6/1/2022	11
Jun-22	6/2/2022	11
Jun-22	6/3/2022	11
Jun-22	6/4/2022	11
Jun-22	6/6/2022	11
Jun-22	6/7/2022	11
Jun-22	6/8/2022	11
Jun-22	6/9/2022	11
Jun-22	6/10/2022	11
Jun-22	6/13/2022	11
Jun-22	6/14/2022	11
Jun-22	6/15/2022	11
Jun-22	6/16/2022	11
Jun-22	6/17/2022	11
Jun-22	6/20/2022	11
Jun-22	6/21/2022	11
Jun-22	6/22/2022	11
Jun-22	6/23/2022	11
Jun-22	6/24/2022	11
Jun-22	6/27/2022	11
Jun-22	6/28/2022	11
Jun-22	6/29/2022	11
Jun-22	6/30/2022	11
Jul-22	7/1/2022	12
Jul-22	7/4/2022	12
Jul-22	7/5/2022	12
Jul-22	7/6/2022	12
Jul-22	7/7/2022	12
Jul-22	7/8/2022	12
Jul-22	7/11/2022	12
Jul-22	7/12/2022	12
Jul-22	7/13/2022	12
Jul-22	7/14/2022	12
Jul-22	7/15/2022	12
Jul-22	7/18/2022	12
Jul-22	7/19/2022	12
Jul-22	7/20/2022	12
Jul-22	7/21/2022	12
Jul-22	7/22/2022	12
Jul-22	7/25/2022	12
Jul-22	7/26/2022	12
Jul-22	7/27/2022	12
Jul-22	7/28/2022	12
Jul-22	7/29/2022	12
Aug-22	8/1/2022	13
Aug-22	8/2/2022	13
Aug-22	8/3/2022	13
Aug-22	8/4/2022	13
Aug-22	8/5/2022	13
Aug-22	8/8/2022	13
Aug-22	8/9/2022	13
Aug-22	8/10/2022	13
Aug-22	8/11/2022	13
Aug-22	8/12/2022	13
Aug-22	8/15/2022	13
Aug-22	8/16/2022	13
Aug-22	8/17/2022	13
Aug-22	8/18/2022	13
Aug-22	8/19/2022	13
Aug-22	8/22/2022	13
Aug-22	8/23/2022	13
Aug-22	8/24/2022	13
Aug-22	8/25/2022	13
Aug-22	8/26/2022	13
Aug-22	8/29/2022	13
Aug-22	8/30/2022	13
Aug-22	8/31/2022	13

What I am now trying to solve for is building this table dynamically through DAX or Power Query. There are three important quirks to how it needs to be built:

1. The five most recent days are always the five previous workdays (Monday-Friday)

2. The most recent weeks and months are the most recent weeks/months for which there is data in another table. I have a measure [SA_Data_Date] that outputs the most recent date for which there is data, or I could reference the [ds] column of the table, where max(SA_Data[ds]) would also give me the date.

The reason for this is that I always have data for the previous days from a less accurate data source, but the weekly and monthly KPIs as well as days for which there is data in the slower but more accurate source are calculated from that second source. So, for example, today (Wednesday of Aug wk 5) I have data from the less accurate source for Monday and Tuesday, but I'm not displaying Aug Wk 5 yet in the weekly view because I have no data from the accurate but delayed source that I'm using to calculate weekly and monthly KPIs.

3. The week count is including partial weeks. For example, Sep Wk 1 is going to be 9/1 through 9/2, and so forth.

What I am struggling with is how to write table creation formulas that dynamically create the right number of rows, name the weeks and months, and create the correct sort order column. Any advice or tricks on how to accomplish this would be appreciated.

Thanks so much!

[Anonymous]

In case anyone is looking at the same issue, here is a first idea: Based on this post https://community.powerbi.com/t5/Desktop/How-to-add-rows-to-a-new-custom-table-based-on-values-in-ot..., the right step might be to create three new calendar tables and crossjoin them. I'll see if I can piece that together from that post.

[Anonymous · Accepted solution]

And to tie this off, it worked! Here is the code to build the table:

DoDWoWMoM_Table = 
var dailycal = ADDCOLUMNS(CALENDAR(TODAY()-8, TODAY()-1),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]),"Category","Day","Testvar",1)
var workingdailycal =  SUMMARIZE(filter(dailycal,isblank([Working Date])=false()),[Working Date],[Category],[Testvar])
var lastdayofSAweek = [SA_Data_Date] - WEEKDAY(SA_Data[SA_Data_Date],3)+4

var weeklycal = ADDCOLUMNS(CALENDAR(lastdayofSAweek-4-7*4, lastdayofSAweek),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]),"Category","Week","Testvar",1 )
var workingweeklycal =  SUMMARIZE(filter(weeklycal,isblank([Working Date])=false()),[Working Date],[Category],[Testvar])
var lastdayofSAmonth = EOMONTH(SA_Data[SA_Data_Date],0)

var firstdayofSAmonthminus3 = EOMONTH(SA_Data[SA_Data_Date],-3)+1
var monthlycal = ADDCOLUMNS(CALENDAR(firstdayofSAmonthminus3, lastdayofSAmonth),"Working Date",if(WEEKDAY([Date]) in {1,7},blank(),[Date]),"Category","Month","Testvar",lastdayofSAmonth)
var workingmonthlycal =  SUMMARIZE(filter(monthlycal,isblank([Working Date])=false()),[Working Date],[Category],[Testvar])
return

union(workingdailycal,workingweeklycal,workingmonthlycal)

Here is the code for naming the columns:

DoDWoWMoM_Name = 
SWITCH([Category],
    "Day",
        FORMAT([Working Date],"Short Date"),
    "Week",
        lookupvalue('dimension-date'[month_name_short],'dimension-date'[date],[Working Date])&" Wk "&1 + WEEKNUM ( [Working Date] )-WEEKNUM( STARTOFMONTH ( DoDWoWMoM_Table[Working Date] )),
    "Month",
        left(LOOKUPVALUE('dimension-date'[month_name],'dimension-date'[date],[Working Date]),3)&"-"&right(LOOKUPVALUE('dimension-date'[year],'dimension-date'[date],[Working Date]),2))

And here is the code for creating the sort columns. Change value to decimal and it does the trick:

Sort_Value = 
SWITCH([Category],
    "Day",
        "0.1"&YEAR([Working Date])&DAY(  [Working Date] ),
    "Week",
        "0.2"&YEAR([Working Date])&WEEKNUM ( [Working Date] ),
    "Month",
        "0.3"&YEAR([Working Date])&MONTH([Working Date]))

There might be a thing or two to troubleshoot, but overall this looks to be doing exactly what I need it to do.