Need help with the DAX calculation to show amounts based on date selection

[honeybee84]

Hi All, 

Thank you very much for reading my post. Really appreciate your help here, as I've been trying to figure out the solution but am stuck. I'm very new to power BI. 

I've attached the sample file, hopefully it is clear enough, but what I'd like to see if the different amounts showing up for forecast depending on the date selection. If a date is selected, the amount should be dynamic to look at raw data and filter from that date, current month or last 3 months. I've tried CALCULATE SUM but it does not give the right number. 

Raw data

Cost Type	Forecast Period	Project	Reporting Period	Amount
Forecast	Jun-24	A	Jul-24	100
Forecast	Jun-24	A	Aug-24	120
Forecast	Jun-24	A	Sep-24	130
Forecast	Jun-24	A	Oct-24	50
Forecast	Jun-24	A	Nov-24	70
Forecast	Jun-24	A	Dec-24	80
Forecast	Jun-24	A	Jan-25	90
Forecast	Jun-24	A	Feb-25	10
Forecast	Jun-24	A	Mar-25	50
Forecast	Jun-24	A	Apr-25	70
Forecast	Jun-24	A	May-25	80
Forecast	Jun-24	A	Jun-25	90
Forecast	Jul-24	A	Jul-24
Forecast	Jul-24	A	Aug-24	110
Forecast	Jul-24	A	Sep-24	90
Forecast	Jul-24	A	Oct-24	80
Forecast	Jul-24	A	Nov-24	70
Forecast	Jul-24	A	Dec-24	60
Forecast	Jul-24	A	Jan-25	120
Forecast	Jul-24	A	Feb-25	110
Forecast	Jul-24	A	Mar-25	100
Forecast	Jul-24	A	Apr-25	90
Forecast	Jul-24	A	May-25	80
Forecast	Jul-24	A	Jun-25	120
Forecast	Aug-24	A	Jul-24
Forecast	Aug-24	A	Aug-24
Forecast	Aug-24	A	Sep-24	80
Forecast	Aug-24	A	Oct-24	70
Forecast	Aug-24	A	Nov-24	100
Forecast	Aug-24	A	Dec-24	70
Forecast	Aug-24	A	Jan-25	60
Forecast	Aug-24	A	Feb-25	50
Forecast	Aug-24	A	Mar-25	70
Forecast	Aug-24	A	Apr-25	80
Forecast	Aug-24	A	May-25	90
Forecast	Aug-24	A	Jun-25	100

Intended Outcome

Any guidance is very appreciated.

Thank you. 

[Anonymous]

Hi @honeybee84 ,

I'm not sure if I get the correct calculation logic... When select "Aug-24" on the slicer 'Reporting Period', then

• Current Month Forecast= Amount which the Forecast Period is "Jul-24" and 'Reporting Period' is "Aug-24" 
• Last 3 Months Forecast = Amount which the Forecast Period is "May-24" and 'Reporting Period' is "Aug-24" 
• Last 6 Months Forecast= Amount which the Forecast Period is "Feb-24" and 'Reporting Period' is "Aug-24" 

Am I right?

Best Regards

[honeybee84]

Yes what you mentioned in your post is exactly what I'm looking for. How to build this logic in power BI? Thank you so much 👏

[honeybee84]

sorry I should say 

Forecast last 3 months (current month being Aug-24 based on "reporting period") should be forecast May-24 for Jun-24, Jul-24, and Aug-24 (sum of 3 months). 

Forecast for last 6 months (current month Aug-24 based on reporting period) should be forecast done in Feb-24 for Mar-24, Apr-24, May-24, Jun-24, Jul-24 and Aug-24 (sum of 6 months).

[Anonymous · Accepted solution]

Hi @honeybee84 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Current Month Forecast = 
VAR _rperiod =
    SELECTEDVALUE ( 'Table'[Reporting Period] )
VAR _year =
    YEAR ( _rperiod )
VAR _month =
    MONTH ( _rperiod )
VAR _sdate =
    DATE ( _year, _month, 1 )
VAR _edate1 =
    EOMONTH ( _sdate, -1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Forecast Period] ) = YEAR ( _edate1 )
                && MONTH ( 'Table'[Forecast Period] ) = MONTH ( _edate1 )
                && 'Table'[Reporting Period] = _rperiod
        )
    )

Last 3 Months Forecast = 
VAR _rperiod =
    SELECTEDVALUE ( 'Table'[Reporting Period] )
VAR _year =
    YEAR ( _rperiod )
VAR _month =
    MONTH ( _rperiod )
VAR _sdate =
    DATE ( _year, _month, 1 )
VAR _edate3 =
    EOMONTH ( _sdate, -3 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Forecast Period] ) = YEAR ( _edate3 )
                && MONTH ( 'Table'[Forecast Period] ) = MONTH ( _edate3 )
                && 'Table'[Reporting Period] >= _edate3 + 1
                && 'Table'[Reporting Period] <= EOMONTH ( _sdate, 0 )
        )
    )

Last 6 Months Forecast = 
VAR _rperiod =
    SELECTEDVALUE ( 'Table'[Reporting Period] )
VAR _year =
    YEAR ( _rperiod )
VAR _month =
    MONTH ( _rperiod )
VAR _sdate =
    DATE ( _year, _month, 1 )
VAR _edate6 =
    EOMONTH ( _sdate, -6 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Forecast Period] ) = YEAR ( _edate6 )
                && MONTH ( 'Table'[Forecast Period] ) = MONTH ( _edate6 )
                && 'Table'[Reporting Period] >= _edate6 + 1
                && 'Table'[Reporting Period] <= EOMONTH ( _sdate, 0 )
        )
    )

Best Regards

[honeybee84]

thank you very much, this works perfectly!

[rajendraongole1]

Hi @honeybee84  - Since you want the amounts to be dynamic based on the selected "Reporting Period" and look back at different months (M-1, M-3, M-6), you can achieve this using DAX measures in Power BI.

Current Month Forecast (M-1)

Please check the attached pbix.

[honeybee84]

Hi @rajendraongole1  thank you so much for helping. Unfortunately I cannot open the pbix file due to my BI version not the latest version (it's a company laptop so I cannot install it freely).