Forum Discussion

superhayan's avatar
superhayan
Helper I
2 years ago

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 g...
superhayan's avatar
superhayan
Helper I
2 years ago

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's avatar
lbendlin
Super User
2 years ago

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

  • superhayan's avatar
    superhayan
    Helper I
    2 years ago

    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 IDInvoiced RevenueRevenue Month (dd/mm/yyyy)
    FF130821-000011219.401/01/2023
    FF130821-000011219.401/03/2024
    FF130821-000011219.401/02/2024
    FF130821-000011219.401/01/2024
    FF130821-000013048.501/12/2020
    FF130821-000013048.501/11/2020
    FF130821-000014267.901/12/2021
    FF130821-000014267.901/11/2021
    FF130821-000014267.901/10/2021
    FF130821-000014267.901/09/2021
    FF130821-000014267.901/08/2021
    FF130821-000013048.501/07/2021
    FF130821-000013048.501/06/2021
    FF130821-000013048.501/05/2021
    FF130821-000013048.501/04/2021
    FF130821-000013048.501/03/2021
    FF130821-000013048.501/02/2021
    FF130821-000013048.501/01/2021
    FF130821-000014267.901/12/2022
    FF130821-000014267.901/11/2022
    FF130821-000014267.901/10/2022
    FF130821-000014267.901/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.