Calcuate next 3 months if an expected date column is 3 months past the selected date slicer

[atowriss1]

I have a table that has revenue, month-end date and expected revenue date.  I am looking to calculate the total revenue based on Expected revenue date.  So, If my slicer is 12/31/2022, I want expected revenue and store count for 30,60 and 90 days out.

My table has an active relationship between ME date and my date calendar, and an inactive relationship between expected revenue date and date calendar.  

The file is evergrowing, meaning, I will have a pipeline report that ends on 10/31/2022, 11/30/2022 and 12/31/2022, that is uploaded as 1 table.  

Here is what I am using currently: 

Store 60 days = calculate(totalMTD(SUM('EC Pipeline'[EV Rooftops]),'EC Pipeline'[ME Date]),

'EC Pipeline'[Expected Rev Date]=date(2023,2,28)).

I tried using the below- but it didn't work (it worked for 30 days, but not 60 or 90

Store 90 days = calculate(totalMTD(SUM('EC Pipeline'[EV Rooftops]),'EC Pipeline'[ME Date]),

USERELATIONSHIP('EC Pipeline'[Expected Rev Date],dimDate[Date]),

DATEADD(dimDate[Date].[Date],+3,MONTH)

)

essentially what I want is the MTD total (based on ME Date), IF the expected revenue date is 30,60 and 90 days after the selected date.

[amitchandak]

@atowriss1 , BAsed on what I got , you can have measure like

Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-60,DAY))

Rolling 90 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY))

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

You can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[amitchandak]

@atowriss1 , BAsed on what I got , you can have measure like

Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-60,DAY))

Rolling 90 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY))

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

You can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc