Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MYDATASTORY
Resolver I
Resolver I

Automatic Month Change on Dial Gauge Date Filter

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 

 

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

really great and Thanks so much😀

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

really great and Thanks so much😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.