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

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.