Dynamically select last month in bar chart

[Anonymous]

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 and also give the option to select bars corresponding to other months if the user wants to see specifically for any month. Is this possible ?

[Anonymous]

Thank You @amitchandak  @v-xuding-msft ...appreciate the help 

[v-xuding-msft · Accepted solution]

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

[amitchandak]

@Anonymous , with only one measure you should be able to do conditional formatting. you can create a measure like this

example

Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")

Then use it in conditional formatting

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

If conditional formatting is not available under the data label. try something like this to put a dot

https://community.powerbi.com/t5/Community-Blog/Display-Label-Only-on-the-Last-Data-Point-of-the-Line-Visual-for/ba-p/1253464