Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |