Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
really great and Thanks so much😀
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |