Forum Discussion

user_007's avatar
user_007
New Member
3 years ago

Date filter

Hi all,

 

I'm trying to build a formulea that gives me the values for the same period in 2019.

 

My report has a date dropdown which the user would use to select a month (e.g. November 2022), and based on this the table will show the relevant data for that period. One of the column in the table i want the values for November 2019 however I can't seem to get this to work. 

 

I have built the below though it shows the total value for 2019, not November 2019:

 

Actual 2019 = CALCULATE(sum('Fusion Data'[Value]), FILTER(ALL('Calendar'),'Calendar'[Year]=2019))
 
Please can someone point out what I need to change for this to work?
 
Thank you
 
Need Help

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    What column do you put to the slicer? The exact the column name and sample value. A small screenshot will be great. 

    • user_007's avatar
      user_007
      New Member
      3 years ago

      Hi FreemanZ 

       

      The slicer is on the date filter, see below screenshot of the table I'm trying to build and also my calendar data

       

       

       

       

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    Hi  user_007 ,

     

    Based on your description, you need to calculate the sum based on the month selected by the slicer.

    You can try:

     

    Actual 2019 = CALCULATE(sum('Fusion Data'[Value]), FILTER(ALL('Calendar'),'Calendar'[Year]=2019 && 'Calendar'[Month] IN VALUES(Slicer fields)))

     

    Because the ALL function is clear about the filter, you need to filter again based on the slicer field.
    If this doesn't solve your question, provide slicer information or sample files

     

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

     

    • user_007's avatar
      user_007
      New Member
      3 years ago

      v-jialluo-msft 

       

      The suggestion didnt work, sure i did something wrong. I've shown a couple a screenshots in my response to FreemanZ . Hopefully it provides the info you need.

       

      Thanks