cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sachet_716 Regular Visitor
Regular Visitor

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  Smiley Sad

 

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 Super Contributor
Super Contributor

Re: MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

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.
Sachet_716 Regular Visitor
Regular Visitor

Re: MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

Hi Yuliana,

 

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

 

MTD.jpg

Regards,

Cristina

v-yulgu-msft Super Contributor
Super Contributor

Re: MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

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.
Sachet_716 Regular Visitor
Regular Visitor

Re: MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

 

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 Super Contributor
Super Contributor

Re: MTD TO SHOW CURRENT MONTH ONLY IGNORING ALL FILTERS

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 285 members 2,741 guests
Please welcome our newest community members: