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

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.