To include a dynamic filter in CALCULATE function

[superhayan]

Hello, I have got a revenue table like below and the Revenue Date ranges from year 2021 to 2024. 

I need to make a dynamic measure that calculates the Sum of Revenue of the last 24 months generated by client invoiced in the same month last year. Using CALCULATE, SUM, DATESINPERIOD, I managed to get the sum of revenue generated in last 24 months. However, I have no clue how to limit it to only include the revenue generated from clients that are invoiced in the same month last year. Please can anyone help?

My ultimate goal is to plot a bar chart with Revenue Month as x-axis and this measure as y-axis. Then the March 2024 bar will show revenue generated during Apr22 to Mar24 that are generated by client invoived in Mar23; while the Febuary 2024 bar will show revenue generated during Mar22 to Feb24 that are generated by client invoived in Feb23... etc

Thank you!

[Anonymous]

Hi @superhayan ,

Thanks for the reply from lbendlin .

The example data you created is not a continuous 21 to 24 years of data, I created a continuous example data:

Date	Value
1/1/2021	9
2/1/2021	16
3/1/2021	11
4/1/2021	20
5/1/2021	12
6/1/2021	6
7/1/2021	14
8/1/2021	7
9/1/2021	19
10/1/2021	11
11/1/2021	2
12/1/2021	2
1/1/2022	18
2/1/2022	18
3/1/2022	18
4/1/2022	8
5/1/2022	13
6/1/2022	17
7/1/2022	6
8/1/2022	7
9/1/2022	3
10/1/2022	19
11/1/2022	14
12/1/2022	18
1/1/2023	1
2/1/2023	20
3/1/2023	4
4/1/2023	20
5/1/2023	12
6/1/2023	11
7/1/2023	15
8/1/2023	16
9/1/2023	10
10/1/2023	17
11/1/2023	16
12/1/2023	2
1/1/2024	6
2/1/2024	2
3/1/2024	19
4/1/2024	16
5/1/2024	11

Create a measure:

MEASURE1 = 
VAR _currentdate =
    MAX ( 'Table1'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Value] ), FILTER ( 'Table1'[Date] ), CALCULATE ( 'Table1'[Date] )
        FILTER (
            ALL ( 'Table1' ), FILTER (
            'Table1'[Date] <= _currentdate
&& 'Table1'[Date]
>= DATE ( YEAR ( _currentdate ), MONTH ( _currentdate ) - 24, DAY ( _currentdate ) )
        )
    )

The page effect is as follows:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[superhayan]

Thanks a lot for your reply. Sorry that I am not be clear enough in the question so I think you might have misunderstood it.

The original data has few thousands of data so I have simplified it. The list of clients are invoiced only on the 1st of every month so you will see the dates are all on the 1st. Some clients are old while some are new or churned already, so not all of them are invoiced throughout the whole period. Basically I want a sum of the Revenue Data with 2 criteria:

1) Rolling 24 months rolling sum (revenue data will be updated once a month)

2) Count the revenue from only the clients invoiced in the corresponding month last year

As the final output is a bar chart of month (x-axis) against rolling revenue sum (y-axis). So each bar will represent the revenue sum of the current 24 months from ONLY the clients that are invoiced that month in the last year. E.g. the March 2024 bar will represent how much the clients who are invoiced in March 2023 are invoiced from April 2022 to March 2024; and the January 2024 bar will show how much the clients who are invoiced in Jan 2023 are invoiced from Feb 2022 to Jan 2024.

Now I can fulfill the 1st criterion with below measure:

= CALCULATE(SUM([Invoiced Revenue]), DATESINPERIOD([Revenue Month], MAX([Revenue Month]),-24, MONTH))

But I don't know how to add the 2nd criterion as I am not filtering the dates directly but the clients based on the dates. Please could you help? Thanks a lot!!!

[lbendlin]

You are making an assumption that your clients are active in both periods. Think of it as a join decision.  Which join do you want, an inner join, a left join, or a full outer join?

[superhayan]

Thanks very much for your reply. What do you mean active in both periods? I might be bit ambiguious in the question so let me explain again sorry. 

The original data has few thousands of data so I have simplified it. The list of clients are invoiced only on the 1st of every month so you will see the dates are all on the 1st. Some clients are old while some are new or churned already, so not all of them are invoiced throughout the whole period. Basically I want a sum of the Revenue Data with 2 criteria:

1) Rolling 24 months rolling sum (revenue data will be updated once a month)

2) Count the revenue from only the clients invoiced in the corresponding month last year

As the final output is a bar chart of month (x-axis) against rolling revenue sum (y-axis). So each bar will represent the revenue sum of the current 24 months from ONLY the clients that are invoiced that month in the last year. E.g. the March 2024 bar will represent how much the clients who are invoiced in March 2023 are invoiced from April 2022 to March 2024; and the January 2024 bar will show how much the clients who are invoiced in Jan 2023 are invoiced from Feb 2022 to Jan 2024.

Now I can fulfill the 1st criterion with below measure:

= CALCULATE(SUM([Invoiced Revenue]), DATESINPERIOD([Revenue Month], MAX([Revenue Month]),-24, MONTH))

But I don't know how to add the 2nd criterion as I am not filtering the dates directly but the clients based on the dates. Please could you help? Thanks a lot!!!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[superhayan]

Thanks for your reply and sorry for pasting the screen shot only. I can't attach file with my permission right and can't upload it to personal drive with my current company laptop. Below are some extract of my original data if that helps.

Client ID	Invoiced Revenue	Revenue Month (dd/mm/yyyy)
FF130821-00001	1219.4	01/01/2023
FF130821-00001	1219.4	01/03/2024
FF130821-00001	1219.4	01/02/2024
FF130821-00001	1219.4	01/01/2024
FF130821-00001	3048.5	01/12/2020
FF130821-00001	3048.5	01/11/2020
FF130821-00001	4267.9	01/12/2021
FF130821-00001	4267.9	01/11/2021
FF130821-00001	4267.9	01/10/2021
FF130821-00001	4267.9	01/09/2021
FF130821-00001	4267.9	01/08/2021
FF130821-00001	3048.5	01/07/2021
FF130821-00001	3048.5	01/06/2021
FF130821-00001	3048.5	01/05/2021
FF130821-00001	3048.5	01/04/2021
FF130821-00001	3048.5	01/03/2021
FF130821-00001	3048.5	01/02/2021
FF130821-00001	3048.5	01/01/2021
FF130821-00001	4267.9	01/12/2022
FF130821-00001	4267.9	01/11/2022
FF130821-00001	4267.9	01/10/2022
FF130821-00001	4267.9	01/09/2022

The final output will be a bar chart of revenue month plotting against the revenue measure based on the 2 criteria mentioned before. The underlying data will be updated once a month with new month's of data coming. in.

Thank you.