Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamically select last month in bar chart

Hello ,   I have a bar chart in Power BI and I am trying to dynamically keep the bar correspoding to last month selected. The intention is to show the numbers for other metrics based on last month ...
Need Help
Show and Tell
Tips and Tricks
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

     

    Please try this:

     

    A disconnected table:

    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    Measures:

    Total sales =
VAR selectedsales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            'Table',
            'Table'[Date].[Month] = SELECTEDVALUE ( 'Date'[Date].[Month] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Month] ),
        selectedsales,
        CALCULATE ( SUM ( 'Table'[Sales] ) )
    )

     

    Measure =
VAR maxdate =
    MAXX ( ALL ( 'Table' ), 'Table'[Date] )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Month] ),
        "Blue",
        IF ( MAX ( 'Table'[Date] ) = maxdate, "Blue", "Gray" )
    )

     

     

    You could reference the document to learn more about conditional formatting and change other colors in the formula.

    https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting#color-based-on-a-calculation