Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Based on Filter Date period Selected calculate next 5 months amount

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!

1 ACCEPTED SOLUTION
Community Support

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

9 REPLIES 9
Community Support

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

Frequent Visitor

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.

Community Support

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

Frequent Visitor

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?

Community Support

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

Solution Sage

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.

Solution Sage

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.

Frequent Visitor

thanks Ross!

Frequent Visitor

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.

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors