Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I would like to highlight MTD sales in a KPI visual, thus, i want to always show MTD figures only regardless of any date filter applied to the report. I used this formula below but whenever other month is chosen, getting sooooooo much frustrated already 😞
MTD SALES = CALCULATE (
SUM ( CVR[Functional Sale Value] ),
FILTER (
ALL ( DimDate[Date]),
DimDate[Date] <= MAX ( DimDate[Date] )
&& MONTH ( DimDate[Date]) = MONTH( ( MAX ( DimDate[Date] ) )
)
))
Seriously need some help. Thank you in advance!
Hi @Sachet_716,
I was confused about your requirement. You said you want to always show MTD figures only regardless of any date filter, but why did you apply date filters in your measure: DimDate[Date] <= MAX ( DimDate[Date] )&& MONTH ( DimDate[Date]) = MONTH( ( MAX ( DimDate[Date] ) )?
Usually, to calculate the MTD value, we use DATESMTD() to assign a MTD dates row set in filter within CALCULATE() funciton. Similar to:
MTD=CALCULATE(SUM(CVR[Functional Sale Value] ), DATESMTD('DimDate[Date]))
Also, please try below formula to test whether it meets your requirement:
MTD SALES = CALCULATE ( SUM ( CVR[Functional Sale Value] ), FILTER ( ALL ( DimDate[Date]), DimDate[Date] <= MAX ( DimDate[Date] ) ) )
For more advice, you 'd better share sample data and show us your desired result if you select a date value in slicer. By the way, do mast sensitive data before uploading.
Regards,
Yuliana Gu
Hi Yuliana,
Thank you for the response. Here is how I wanted it:
Regards,
Cristina
Hi @Sachet_716,
Please try this:
MTD SALES = CALCULATE ( SUM ( CVR[Functional Sale Value] ), FILTER ( ALL ( DimDate[Date] ), DimDate[Date] <= TODAY () && MONTH ( DimDate[Date] ) = MONTH ( TODAY () ) ) )
Best regards,
Yuliana Gu
Thanks Yuliana,
It gives the ssames result, I just cant post a screen shot, dont know why. Perhaps my requirement is not possible?
Rgds, Cristina
Hi @Sachet_716,
Would you please share the .pbix file? Do mask sensitive data before shring.
Regards,
Yuliana Gu
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |