CODEWITHSUNDEEP
powerbidaxbusiness-intelligence
João Vitor Degrandi
João Vitor Degrandi

Reputation: 4155

Can't filter properly using a measure as Date in POWER BI

I'm trying to do a report where I have a measure ([10 Days]) with a date stored in it, and I'm using this formula where the measure is necessary as a filter:

VOL NEXT 10 DAYS = CALCULATE([SUM VOLUME];FILTER(fOrders; fOrders[Dates] >= TODAY()); FILTER(fOrders; fOrders[Dates] <= [10 Days]))

The objective is to get the sum of all the volume (which I did using SUMX) and filter it within today and a specific day.

The [10 Days] has to be a measure because is not 10 days straight, but not counting off days from production.

I created a card and the measure shows the intended date, but when I put at the formula, the output is wrong. And if I put the date in the format DATE(YYYY;MM;DD) it works just fine. But I tried DATE(YEAR([10 Days]);MONTH([10 Days]);DAYS([10 Days])) and the sum was still using the wrong date.

Upvotes: 0

Views: 351

Answers (1)

CR7SMS
CR7SMS

Reputation: 2584

You can pass the measure value to a variable and then apply the filter. The following calculation should get you started:

Volume Sum = 
       VAR EndDate = [10 Days]
Return CALCULATE(SUM(fOrders[Volume]),fOrders[Dates]>=TODAY()&&fOrders[Dates]<=EndDate)

Note: The above formula is written under the assumption that [10 Days] is always higher than today. If it's not then modify the formula accordingly.

Hope this helps.

Upvotes: 1

Related Questions