Difference in measure result in filter context: Dynamic filter

[Anonymous]

Hi

I am using the measure below to return a daily sales revenue amount to a matrix visual.

Daily Revenue = CALCULATE(SUM(ALLRevenue[Spot Margin in LCY]),FILTER(ALLRevenue,ALLRevenue[Trade Date]=MAX(dDate[Date])))

The ALLRevenue table has data for year 2018 and YTD 2019 to 30 Sep. I also have a date table in the model which I have related to the Trade Date field in the ALLRevenue table. A slicer for the Month name (based on the date table) is also included.  I have noticed that the measure gives an incorrect result if, for instance, I select a month earlier than on the slicer Sep 2019. It also yields a result if I select a month later than September.

I have also observed that hard coding the date (using the DATE function below) yields the expected result i.e. no results earlier or after 30 September.

Daily Revenue = CALCULATE(SUM(ALLRevenue[Spot Margin in LCY]),FILTER(ALLRevenue,ALLRevenue[Trade Date]=DATE(2019,09,30)))

Please advise on the best we to constract this measure to dynamically filter the data using the dates available.

Thanks 

David 

[v-juanli-msft]

Hi @Anonymous 

If you want to select a month from a slicer, then show values for dates in this month,

please create relationships between tables as below, 

then add "date" from "date table" and "value" from "your table"

If not, what result do you expect?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.