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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jpbi23
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, 

 

jpbi23_1-1709762834245.png

 

 

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 

 

100200100700

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 

100700800400

100

 

Thanks in advance for any assistance!

1 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

vheqmsft_0-1710311623145.png

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

 

View solution in original post

9 REPLIES 9
v-heq-msft
Community Support
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:

vheqmsft_0-1709794360024.png

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

vheqmsft_1-1709794633199.png

vheqmsft_2-1709794658584.png

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

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, 

jpbi23_0-1710256308069.png

 

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. 

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

vheqmsft_0-1710311623145.png

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

 

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?

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
Solution Specialist
Solution Specialist

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
Solution Specialist
Solution Specialist

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.

 

 

thanks Ross!

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.