Forum Discussion
To include a dynamic filter in CALCULATE function
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!
6 Replies
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?
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!!!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-Forum/ba-p/963216
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/1447523
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 TeamIf 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!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!!!
