Date filter

[user_007]

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

[v-jialluo-msft]

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]

@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

[FreemanZ]

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

[user_007]

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