Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Difference in measure result in filter context: Dynamic filter

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 
Need Help
Tips and Tricks

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.