Forum Discussion
To include a dynamic filter in CALCULATE function
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!
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: