Measure with sum if - multiple conditions and date range in between

[a-5]

Hi! 

I'm trying to create a measure in which I calculate the pipeline value for product A for the current month + 3 following months. I have tried to create the measure with the below formula, but it doesn't work. 

sum({$<[Product]={'A'},[Date]={"<=$Date(MonthEnd(AddMonths(Today(),+3)),'DD.MM.YYYY')>=$Date(MonthStart(Today()),'DD.MM.YYYY'))"}>} [Pipeline value])

Can you please assist?

Thank you in advance!

[vincent_ardiet_ · Accepted solution]

Try like this:
sum({$<[Product]={'A'},[Date]={"<=$(=Date(MonthEnd(AddMonths(Today(),+3)),'DD.MM.YYYY'))>=$(=Date(MonthStart(Today()),'DD.MM.YYYY')))"}>} [Pipeline value])

[a-5]

Thank you for your comment!

When applying this formula, it works well for the future (next 3months are included only), but not for the historical months. It brings e.g-. previous months numbers also even though I only want to include the current month. 

Any idea how to solve this?

[vincent_ardiet_]

Sorry I don't understand, could you give examples? You were saying initially that you want: "the current month + 3 following months".

[a-5]

Yes so I want to include the current month + 3 following months (Oct/2023-Jan/2024 when looking at the data today). But now  after using this formula, I also got data for Sep/2023, which shouldn't be the case.

Is there still something wrong with the bolded part of the formula?

sum({$<[Product]={'A'},[Date]={"<=$(=Date(MonthEnd(AddMonths(Today(),+3)),'DD.MM.YYYY'))>=$(=Date(MonthStart(Today()),'DD.MM.YYYY')))"}>} [Pipeline value])

[vincent_ardiet_]

Are you sure that you field [Date] stores the value in the format DD.MM.YYYY?
Else I don't see what is going wrong.
You can also try to enter the $(=...) formulas in a text object to be sure that the result returned is correct, or in a search bar.