Automatic Month Change on Dial Gauge Date Filter

[MYDATASTORY]

Hi, 

I was just wondering if anyone can advise on how to set an automatic filter on Dial Guage on Date filter to change automatically when Month-end and set the sales to zero i.e, sum(Sales) which give total sales.

i.e I have a column with Total Sales and  Sales Date, I want the sales to reset to zero automatically once a new month start.

At the end of the month 31/10/2019:         Oct Total Sales = $100 

At the beginning of the Month 0/11/2019: Nov Total sales = $ 0

Thanks so much 

[v-shex-msft · Accepted solution]

HI @MYDATASTORY ,

According to your description, It sounds like you want to achieve monthly rolling calculation on gauge visual. You can try to write a measure with conditions on month and date based on selected date:

Measure =
VAR currDate =
    MAX ( Date[Date] ) // seleceted calendar date
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            'Table'[Date] <= currDate
                && MONTH ( Table[Date] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

HI @MYDATASTORY ,

According to your description, It sounds like you want to achieve monthly rolling calculation on gauge visual. You can try to write a measure with conditions on month and date based on selected date:

Measure =
VAR currDate =
    MAX ( Date[Date] ) // seleceted calendar date
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            'Table'[Date] <= currDate
                && MONTH ( Table[Date] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng

[MYDATASTORY · Accepted solution]

really great and Thanks so much😀