Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Table Visual With Dynamic Dates Across the Top

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

     "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.

9 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        lbendlin
        Super User
        4 years ago

        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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.