Forum Discussion

powerbiAG's avatar
powerbiAG
Frequent Visitor
3 years ago
Solved

Calculation based on 2 measures across different months.

 

Hi All,

We have a scenario where we need to do a calculation based on 2 measures across different months.

Let me explain with example..

 

We have a monthly Sales table which has Actual Sales data.

 

MonthSales
Oct-222200
Nov-223800
Dec-224200
Jan-234000

 

There is a Projected Bookings table with projected sales numbers for each month.

 

MonthProjected Bookings
Oct-221.2
Nov-221
Dec-221.1
Jan-230.9
Feb-231.1
Mar-230.8
Apr-231.3

 

The requirement is to populate Projected Sales for next 3 months (after the last month available in the Sales table) 

 

In this example since the last month available in Sales table is Jan 2023 --> the Projected Sales would have values for Feb 2023, Mar 2023 and Apr 2023.

 

MonthProjected Sales
Feb-234400
Mar-233200
Apr-235200

 

To calculate the values of Projected Sales --> we need to take the Sales of last available month from Sales table ( which is Jan 2023 with value of 4000) and multiply with the respective monthly Projected Bookings value for only future months...

Feb 2023 (Projected) Sales  = Jan 2023 Sales *  Feb 2023 Projected Bookings( 4000 * 1.1 = 4400 )
Mar 2023 (Projected) Sales = Jan 2023 Sales *  Mar 2023 Projected Bookings( 4000 * 0.8 = 3200 )
Apr 2023 (Projected) Sales = Jan 2023 Sales *  Apr 2023 Projected Bookings( 4000 * 1.3 = 5200 )

 

Any guidance or pointers towards a solution would be highly appreciated.

 

Thanks.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I do not know how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

     

     

    Projected sales: =
VAR _latestmonthdate =
    CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _latestmonth =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _latestmonthdate ),
        'Calendar'[Month-Year]
    )
VAR _latestmonthsales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year] = _latestmonth )
    )
VAR _projectedbookings =
    MAX ( Projected[Projected bookings] )
RETURN
    IF ( ISBLANK ( SUM ( Sales[Sales] ) ), _latestmonthsales * _projectedbookings )

     

     

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I do not know how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    It is for creating a measure.

     

     

     

     

    Projected sales: =
VAR _latestmonthdate =
    CALCULATE ( MAX ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _latestmonth =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _latestmonthdate ),
        'Calendar'[Month-Year]
    )
VAR _latestmonthsales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Month-Year] = _latestmonth )
    )
VAR _projectedbookings =
    MAX ( Projected[Projected bookings] )
RETURN
    IF ( ISBLANK ( SUM ( Sales[Sales] ) ), _latestmonthsales * _projectedbookings )