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
Sachet_716
Helper I
Helper I

MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

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!

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana,

 

Thank you for the response. Here is how I wanted it:

 

MTD.jpg

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.