Table Visual With Dynamic Dates Across the Top

[Pete_Manley]

I am trying to create a report that would show values for various columns where the column headers would be dates. We do this sort of thing without much problem in Excel. But I can't see how I would implement this using the Power BI Visuals. The data would be coming from SQL Server via Direct Query, therefore I can pass over whatever I desire, but I can't see how to present the data so that the column headers would change to the date. For example if I want to show total workforce by project for each week in the last month, how do I show the dates for each of the weeks? Can the table dynamically change the headers to show the date of the week? Here there could be four or five weeks per month, which also is an issue.

Here is a simple example

Any suggestions appreciated, thanks

[Pete_Manley]

Looks to me like I can do something similar to what is required using a Power BI Matrix. This would appear to be the best solution for me. Any other ideas or suggestions please feel free to post on here. Thanks, Peter

[v-rzhou-msft]

Hi @Pete_Manley 

We need to create a measure to dynamic filter our visual to show values last month. Filter couldn't work if your Date column is in Row Field. Here I suggest you to add Date column into Matrix Row Field instead of Columns Field. 

Firstly create a Date table by Dax.

Date = 
VAR _Basic =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] ),
        "Day", DAY ( [Date] ),
        "DayName", FORMAT ( [Date], "DDDD" )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _Basic,
        "DayName First Day",
            MAXX (
                FILTER ( _Basic, [YearMonth] = EARLIER ( [YearMonth] ) && [Day] = 1 ),
                [DayName]
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Group",
            MAXX (
                FILTER (
                    _ADD1,
                    [YearMonth] = EARLIER ( [YearMonth] )
                        && [DayName] = EARLIER ( [DayName First Day] )
                        && [Date] <= EARLIER ( [Date] )
                ),
                [Date]
            )
    )
RETURN
    _ADD2

Create a relationship between your Data table and Date table.

Then create a filter measure.

Measure = 
VAR _CURRENTYEAR = YEAR(TODAY())
VAR _CURRENTMONTH = MONTH(TODAY())
RETURN
IF(MAX('Date'[Year]) = _CURRENTYEAR&&MAX('Date'[Month]) = _CURRENTMONTH-1,1,0)

Result is as below.

If you have different years, you need to update your Date table and measure. Add a Rank column in Date table based on YearMonth. Then Get current YearMonth, and calculate the rank based on Current YearMonth and get Rank -1 to show 1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pete_Manley]

What you have suggested there is not really suitable for me. Mainly because my requirement was for dates across the top. But also I can't be going in editing things every time the dates change "If you have different years, you need to update your Date table and measure", the dates used can be preselected in SQL Server and passed to me already filtered by Direct Query. Your proposed solution appears to me quite complicated compared to simply using the Power BI Matrix visual.  Thanks for the suggestion.

[v-rzhou-msft · Accepted solution]

Hi @Pete_Manley 

 "If you have different years, you need to update your Date table and measure"  This doesn't mean that you need to edit your code everytime you refresh you data. I mean that my sample only has data in one year, so I didn't consider the conditions in different year in my code, you may need to update your code. If you have data between 2021/12 to 2022/01..., you need to update the code.

Here is the new code which can be used in all situations.

New Date table.

Date = 
VAR _Basic =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "YearMonth",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] ),
        "Day", DAY ( [Date] ),
        "DayName", FORMAT ( [Date], "DDDD" )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _Basic,
        "DayName First Day",
            MAXX (
                FILTER ( _Basic, [YearMonth] = EARLIER ( [YearMonth] ) && [Day] = 1 ),
                [DayName]
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Group",
            MAXX (
                FILTER (
                    _ADD1,
                    [YearMonth] = EARLIER ( [YearMonth] )
                        && [DayName] = EARLIER ( [DayName First Day] )
                        && [Date] <= EARLIER ( [Date] )
                ),
                [Date]
            )
    )
VAR _ADDRANK =
    ADDCOLUMNS ( _ADD2, "RankYearMonth", RANKX ( _ADD2, [YearMonth],, ASC, DENSE ) )
RETURN
    _ADDRANK

New Filter Measure.

Measure = 
VAR _CURRENTYEARMONTH = YEAR(TODAY())*100+MONTH(TODAY())
VAR _CURRENTRANK = CALCULATE(MAX('Date'[RankYearMonth]),FILTER(ALL('Date'),'Date'[YearMonth] = _CURRENTYEARMONTH))
RETURN
IF(MAX('Date'[RankYearMonth]) = _CURRENTRANK-1,1,0)

You can use this way to filter your visual anytime to show values in last month.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

That is standard functionality - add the date field to the columns well.

If you want to get fancy you can use SVG code to turn the column headers 90 degrees. I can provide examples if needed.

[Pete_Manley]

Ibendlin, thanks for the reply, but it is not clear to me what you mean. I can add the date fields as columns in the query supplying the data to Power BI, yes that part I understand, but how do the dates in the column become the headears in the Power BI table visual? Can you please provide more detail on how you move from the dates being in a column to being a header on the visual? Perhaps a link to somewhere where how to do that is explaind? Thanks, Peter

[lbendlin]

Maybe your source data is from Excel and needs to be unpivoted.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[Pete_Manley]

I am not using Excel or Pivot Tables. The data is comming to Power BI from SQL Server via Direct Query, I can send through the data in any structure required since I am writing the queries myself. What I don't know is how to change the data that Power BI recieves into somthing that has dates across the top of the table. So the data table in Power BI could have dates as a column, but how do they become headers on a Power BI table visual? Is it even possible to do that? Thanks, Peter

[lbendlin]

My bad, I read "matrix visual"  the whole time.  You can't do that with a table visual, but it is standard functionality of a matrix visual.