Filter with start date and MTD less than today

[Karthik12]

Hello Team,

I am trying to create a measure, which filters a sum from 27.12.2022 to < today (i,e) 26.01.2023. Start date is constant but End date should auto update daily to < today. 

My measure as follows

Test MTD sum = CALCULATE(SUM('mis monthly_plan'[plan]),FILTER('mis monthly_plan','mis monthly_plan'[date]>DATE(2023,1,1)&&'mis monthly_plan'[date]<DATESMTD(CALENDAR(TODAY())

But there was an error. Can you support for correction?

[v-zhangti · Accepted solution]

Hi, @Karthik12 

You can try the following methods.

Test MTD sum =
CALCULATE (
    SUM ( 'mis monthly_plan'[plan] ),
    FILTER (
        'mis monthly_plan',
        'mis monthly_plan'[date] > DATE ( 2023, 1, 1 )
            && 'mis monthly_plan'[date] < TODAY ()
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Karthik12 ,

I recommend reading this article as it contains almost everything about date/datetime calculations: Time patterns – DAX Patterns
I also recommend using a dedicated calendar table, how to create this table and why it's considered a best practice is also explained in the article.

Regards,

Tom

[Karthik12]

@TomMartens Thanks. I go through the topics. It quite useful to me for further learning.

Still, am searching for the best solution to my problem.