Based on Filter Date period Selected calculate next 5 months amount

[jpbi23]

Here is my table, MasterRevenue

Period

Revenue

11/1/2023	$    100.00
12/1/2023	$    200.00
1/1/2024	$    100.00
2/1/2024	$    700.00
3/1/2024	$    800.00
4/1/2024	$    400.00
5/1/2024	$    100.00
6/1/2024	$    100.00
7/1/2024	$    300.00
8/1/2024	$    250.00
9/1/2024	$    100.00
10/1/2024	$    100.00
11/1/2024	$    100.00
12/1/2024	$    800.00
1/1/2025	$    100.00
2/1/2024	$    900.00

I want to use period as a date slicer and based on what date the user selects, I want to calculate total for next 5 months total as new columns. Something like this, 

If the user selected 11/1/2023, then it would take the mmounts for 11/1/2023 -3/1/2024 (current and next 4 months based on selected date) and split them out this way, 

Month 1	Month 2	Month 3	Month 4	Month 5
100	200	100	700	800

If the user selected 1/1/2024, then it would take amounts for 1/1/2024 -5/1/2024 (current and next 4 months based on selected date) and split them out this way, 

Month 1	Month 2	Month 3	Month 4	Month 5
100	700	800	400	100

Thanks in advance for any assistance!

[v-heq-msft]

Hi @jpbi23 ,
Thanks to  @RossEdwards for the great thought that you may need to create a measure for each Month.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a date table based on the dates in the original table

Date = VALUES(MasterRevenue[Period])

Create a MEASURE for each month

Month 1 = 
CALCULATE(
    MAX(MasterRevenue[Revenue]),
    FILTER(
        MasterRevenue,
        MasterRevenue[Period] = SELECTEDVALUE('Date'[Date])
    )
)

Month 2 = 
CALCULATE(
    MAX(MasterRevenue[Revenue]),
    FILTER(
        MasterRevenue,
        MasterRevenue[Period] = EDATE(SELECTEDVALUE('Date'[Date]),1)
        //1= Month (Number-1),Month 2 = 1, Month 3 = 2 ...
    )
)

3. Create a meaure to calculate the sum of 5 months

Total Revenue Next 5 Months = 
VAR SelectedDate = MAX('Date'[Date])
VAR EndDate = EDATE(SelectedDate, 4)
RETURN
CALCULATE(
    SUM('MasterRevenue'[Revenue]),
    FILTER(
        ALL(MasterRevenue),
        'MasterRevenue'[Period] >= SelectedDate && 'MasterRevenue'[Period] <= EndDate
    )
)

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jpbi23]

Hi Albert, 

Thanks so much! I have one follow up to this. Lets say I have revenue in the future going to 2026, 2027, 2028 etc. And I need to do one column that sums up future revenue basesd on the date selected. 

For example, 

Instead of Total revenue for 5 months, I need 

1st 12 monhts - this will calculate remaining revenue for next 12 months (after month 5)

2nd 12 months - this will calculate the following 12 monhts after '1st 12 monhts'

This will give me revenue for next 24 months based on the selected date. 

Thanks so much for your help. 

[v-heq-msft · Accepted solution]

Hi @jpbi23 ,
Create two measures

Total Revenue Next 12 Months (after 5 months) = 
VAR SelectedDate = MAX('Date'[Date])
Var StartDate = EDATE(SelectedDate,5)
VAR EndDate = EDATE(SelectedDate, 17)
RETURN
CALCULATE(
    SUM('MasterRevenue'[Revenue]),
    FILTER(
        ALL(MasterRevenue),
        'MasterRevenue'[Period] >= StartDate && 'MasterRevenue'[Period] < EndDate
    )
)

Total Revenue Next 13-24 Months (after 5 months) = 
VAR SelectedDate = MAX('Date'[Date])
Var StartDate = EDATE(SelectedDate,17)
VAR EndDate = EDATE(SelectedDate, 29)
RETURN
CALCULATE(
    SUM('MasterRevenue'[Revenue]),
    FILTER(
        ALL(MasterRevenue),
        'MasterRevenue'[Period] >= StartDate && 'MasterRevenue'[Period] < EndDate
    )
)

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jpbi23]

Thanks Albert. 

If I have another field to this, lets say Order ID. Total Revenue for next 12 months is summing everything up on the table for all Order IDs. I need to happen at the row level. So Total Revenue for next 12 months is showing only revenue for each Order ID. 

OrderID is also coming from the MasterRevenue table.

Currently this calculation is summing up the whole table. Is there a way around this?

[v-heq-msft]

Hi @jpbi23 ,
Based on your description, you can use measure on these in the form this will filter at the row level. Your initial issue seems to be resolved, if there is another issue you can post a new thread. This way if other users have the same question they can find the answer more clearly.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RossEdwards]

The reason I used "Min" is that in your example it looks like you've used a slicer selecer for "Greater Than" rather than a single selection.  "Min" should mean that whatever date the user has selected, this will always return the lowest value which is where i'm expecting you want to start doing the Month +1, +2 etc from.

To your 2nd question, yes we are resolving what date the user has selected and then for the "Month 1" we are treating that as "Selected Date + 1 Month".  Naturally if this isn't quite what you were chasing, hopefully the method gives you clues on what to change to get the method you want.

[RossEdwards]

One method could be to write 5 measures, 1 for each future months.

Month 1 = var contextDate = DATEADD(MIN('Example'[Period]), 1, MONTH)
var output = CALCULATE(
    SUM('Example'[Revenue]),
    ALL('Example'),
    'Example'[Period] = contextDate
)
RETURN
output

Then for the Month measures 2 to 5, change the parameter in the DATEADD from 1 to the 2 to 5 value.

[jpbi23]

thanks Ross!

[jpbi23]

the contextdate var isn't working. Is the syntax correct? Seems like it's an issue with MIN?

Also the var is getting the Minimum date and moving a month? I need to get the date that the user selects in the filter and total the revenue for the next 5 months.