MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

[Sachet_716]

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!

[v-yulgu-msft]

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

[Sachet_716]

Hi Yuliana,

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

Regards,

Cristina

[v-yulgu-msft]

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

[Sachet_716]

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

[v-yulgu-msft]

Hi @Sachet_716,

Would you please share the .pbix file? Do mask sensitive data before shring.

Regards,

Yuliana Gu