Dynamic Matrix Table

[williamy281]

I'm trying create a dynamic matrix table that will only show data with values.   Below, is how this would work, so if the data is ran on Monday, we want all data up to the Sunday (since Monday's data wouldn't be available). Then, on Tuesday, it would show all data up to the Monday., etc. so days would only show the last date of data.

[edhans · Accepted solution]

Hi @williamy281 - I think I have it. This is about a 90% modeling issue, and 10% DAX and Matrix visual. This is the final result:

To look a this, here is the last week of data, which I had to fake. Your data stopped at Aug 13. I needed data through yesterday so the clock worked.

So today is Tuesday, Aug 25. The last row of the matrix shows a week starting on Tues the 18th and running through Monday the 24th. I had to do a few things.

First, I needed actual dates for your data. You can see those transformations in my PBIX file, but I needed to add the day number based on the row of your "report week" which has a repeated date, which is no good. I had to do some calculations to show the first week correctly if it isn't a full week (7 days) and the last week as well. Different calcs for each but you can see the M code in the file.

The code for just the day number is:

    #"Added Day Number" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Custom", 
            each 
                    let
                        varRowCount = Table.RowCount([AllRows]),
                        varCurrentDate = [Report Week]
                    in
                Table.AddIndexColumn(
                [AllRows], 
                "DayNumber",
                if varRowCount < 7
                then 
                    if List.Min(#"Grouped Rows"[Report Week]) = varCurrentDate
                    then 7-varRowCount
                    else 0
                else 0

                )
            )

Now, I had to have a good Date table that was equally dynamic. The Report Week gets moved to there. Always use a date table for dates. Full M code for the date table is:

let
    Source = 
        {
            Number.From(
            #date(
                Date.Year(
                    List.Min(Table[Date])
                    ),1,1
                )
            )..
            Number.From(
                #date(
                    Date.Year(
                        List.Max(Table[Date])
                        ),12,31)
                    )
        },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Added Short Month Name" = Table.AddColumn(#"Inserted Month Name", "Short Month Name", each Text.Start([Month Name],3), type text),
    #"Added Month Year" = Table.AddColumn(#"Added Short Month Name", "Month Year", each [Short Month Name] & " " & Text.From([Year]), type text),
    #"Inserted Quarter Number" = Table.AddColumn(#"Added Month Year", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn( #"Inserted Quarter Number","Quarter", each "Qtr " & Text.From([Quarter Number]), type text),
    #"Inserted Quarter Year Sort" = Table.AddColumn(#"Inserted Quarter", "Quarter Year Sort", each [Year] * 10 + [Quarter Number], Int64.Type),
    #"Inserted Quarter Year" = Table.AddColumn(#"Inserted Quarter Year Sort", "Quarter Year", each "Q" & Text.End([Quarter],1) & " " & Text.From([Year]), type text),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter Year", "Week of Year", each Date.WeekOfYear([Date], Day.Thursday), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), Int64.Type),
    // Dynamically calculated each day
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date], Date.DayOfWeek((DateTime.Date(DateTime.LocalNow())))), Int64.Type),
    #"Added Day of Week Sort" = Table.AddColumn(#"Inserted Day of Week", "Day of Week Sort", each [Day of Week] * -1, Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Added Day of Week Sort", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added IsFuture Boolean" = Table.AddColumn(#"Inserted Day Name", "IsFuture", each [Date] > DateTime.Date(DateTime.LocalNow()), type logical),
    #"Added IsInCurrentWeek" = Table.AddColumn(#"Added IsFuture Boolean", "IsInCurrentWeek", each Date.IsInCurrentWeek([Date]), type logical),
    #"Added IsInCurrentMonth" = Table.AddColumn(#"Added IsInCurrentWeek", "IsInCurrentMonth", each Date.IsInCurrentMonth([Date]), type logical),
    #"Added IsInCurrentQuarter" = Table.AddColumn(#"Added IsInCurrentMonth", "IsInCurrentQuarter", each Date.IsInCurrentQuarter([Date]), type logical),
    #"Added IsInCurrentYear" = Table.AddColumn(#"Added IsInCurrentQuarter","IsInCurrentYear", each Date.IsInCurrentYear([Date]), type logical),
    #"Added First Day of Week" = Table.AddColumn(#"Added IsInCurrentYear", "First Day of Week", each Date.StartOfWeek([Date], Date.DayOfWeek((DateTime.Date(DateTime.LocalNow())))), type date),
    // Dynamically calculated every day
    #"Added Report Week" = 
        Table.AddColumn(
            #"Added First Day of Week", 
            "Report Week", 
            each 
                let
                    varFirstDayOfWeek = Date.DayOfWeek((DateTime.Date(DateTime.LocalNow())))
                in
            Text.From(Date.StartOfWeek([Date], varFirstDayOfWeek)) & " - " & Text.From(Date.AddDays(Date.StartOfWeek([Date], varFirstDayOfWeek), 6)),
            type text)
in
    #"Added Report Week"

Things to note on the date table:

1. It is a full calendar year based on your data - note Source row gets its data from the min/max dates in your model then moves it to Jan 1 and Dec 31
2. The Day of Week is dynamic based on today (refresh date) and is used to sort the weekday names - Date.DayOfWeek([Date], Date.DayOfWeek((DateTime.Date(DateTime.LocalNow()))))
3. The Report Week (used in the matrix visual) is also dynamic, which you can see above - it is the last step.

Here is the PBIX.
This would have been much much easier had your source data had a real date in it, but I am assuming you are stuck with the export you are getting.

[Anonymous]

HI @williamy281,

You can create a calculated table with all day of week value and setting up a default sort order, then you can add calculated column based on the last date from raw table and use 'day of week' value to calculate out the dynamic sort order and use as the sort order of new 'day of week' field.
After these steps, you can use 'day of week' to link raw table fields and use on the matrix to achieve dynamic sort order of columns field.

Custom Sorting in Power BI 

Regards,

Xiaoxin Sheng

[amitchandak]

@williamy281 , You have select this week data and take lastnonblankvaluel

you can use week rank for that

column in date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measure
This Week = CALCULATE(lastnonblankvalue('Date'[date],sum('table'[Qty])), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

for week refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[edhans]

Some test data would be helpful.

Much of this could be done using transformations in Power Query. For example, you need to sort your days of the week dynamically, where today should be last and 7 days ago would be first. The Date.DayOfWeek() function could be manipulated to also subtract today's Day of Week number to change the sort order. Then use the Sort By Columns feature to sort your days by this sort column. 

You can also use this column to limit what you bring into the model by filtering dynamically in Power Query.

Then the DAX and visual becomes easy.

But again, sample data with some actual expected output, not just "X" place holders, would really be needed to assist in building this logic.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[williamy281]

I'm not sure if this is helpful, but the data currently looks like this:

There is not data for Wednesday because that's the day that I ran the data.

[edhans]

Hi @williamy281 - I'll post the link again. I am not typing that data in. Needs to be in a table format. Please see links below. Thanks!

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[williamy281]

Report Week	Week	Day of the Week	total
7/2/2020 0:00	7/2/2020 - 7/8/2020	Monday	6524
7/2/2020 0:00	7/2/2020 - 7/8/2020	Tuesday	6540
7/2/2020 0:00	7/2/2020 - 7/8/2020	Wednesday	6529
7/9/2020 0:00	7/9/2020 - 7/15/2020	Thursday	6032
7/9/2020 0:00	7/9/2020 - 7/15/2020	Friday	5688
7/9/2020 0:00	7/9/2020 - 7/15/2020	Saturday	1064
7/9/2020 0:00	7/9/2020 - 7/15/2020	Sunday	1313
7/9/2020 0:00	7/9/2020 - 7/15/2020	Monday	7468
7/9/2020 0:00	7/9/2020 - 7/15/2020	Tuesday	6586
7/9/2020 0:00	7/9/2020 - 7/15/2020	Wednesday	5998
7/16/2020 0:00	7/16/2020 - 7/22/2020	Thursday	6326
7/16/2020 0:00	7/16/2020 - 7/22/2020	Friday	5512
7/16/2020 0:00	7/16/2020 - 7/22/2020	Saturday	1057
7/16/2020 0:00	7/16/2020 - 7/22/2020	Sunday	1098
7/16/2020 0:00	7/16/2020 - 7/22/2020	Monday	7122
7/16/2020 0:00	7/16/2020 - 7/22/2020	Tuesday	6317
7/16/2020 0:00	7/16/2020 - 7/22/2020	Wednesday	6608
7/23/2020 0:00	7/23/2020 - 7/29/2020	Thursday	5969
7/23/2020 0:00	7/23/2020 - 7/29/2020	Friday	5884
7/23/2020 0:00	7/23/2020 - 7/29/2020	Saturday	1950
7/23/2020 0:00	7/23/2020 - 7/29/2020	Sunday	2238
7/23/2020 0:00	7/23/2020 - 7/29/2020	Monday	14409
7/23/2020 0:00	7/23/2020 - 7/29/2020	Tuesday	13192
7/23/2020 0:00	7/23/2020 - 7/29/2020	Wednesday	12976
7/30/2020 0:00	7/30/2020 - 8/5/2020	Thursday	12063
7/30/2020 0:00	7/30/2020 - 8/5/2020	Friday	10304
7/30/2020 0:00	7/30/2020 - 8/5/2020	Saturday	1004
7/30/2020 0:00	7/30/2020 - 8/5/2020	Sunday	1105
7/30/2020 0:00	7/30/2020 - 8/5/2020	Monday	7052
7/30/2020 0:00	7/30/2020 - 8/5/2020	Tuesday	6202
7/30/2020 0:00	7/30/2020 - 8/5/2020	Wednesday	5834
8/6/2020 0:00	8/6/2020 - 8/12/2020	Thursday	11660
8/6/2020 0:00	8/6/2020 - 8/12/2020	Friday	11690
8/6/2020 0:00	8/6/2020 - 8/12/2020	Saturday	2234
8/6/2020 0:00	8/6/2020 - 8/12/2020	Sunday	2354
8/6/2020 0:00	8/6/2020 - 8/12/2020	Monday	13555
8/6/2020 0:00	8/6/2020 - 8/12/2020	Tuesday	10933
8/6/2020 0:00	8/6/2020 - 8/12/2020	Wednesday	6105
8/13/2020 0:00	8/13/2020 - 8/19/2020	Thursday	6143
8/13/2020 0:00	8/13/2020 - 8/19/2020	Friday	5632
8/13/2020 0:00	8/13/2020 - 8/19/2020	Saturday	1106
8/13/2020 0:00	8/13/2020 - 8/19/2020	Sunday	1082
8/13/2020 0:00	8/13/2020 - 8/19/2020	Monday	7082
8/13/2020 0:00	8/13/2020 - 8/19/2020	Tuesday	4750