Forum Discussion

kristel_tulio's avatar
kristel_tulio
Helper III
3 years ago

Full Year Projection

Hi, I would like to ask your help in getting 

Full year projection = Current year actuals for selected months + prior year actuals for the months which have not been selected.

I have this measure "P & L Value" to be used in calculation.

I'm using the Date table for the dates

 

Example:

This Year we have January - May values selected then will add to last year months that is not selected on my date slicer

 

If you have any suggestion how to do it.

 

 

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi kristel_tulio   I created Excel from your picture data and following measures:

    Adjust "Sheet9" to your table

    1. M_P&L = SUM(Sheet9[P&L Value])

    2. P&L_MTD =

        CALCULATE([M_P&L],
        DATESMTD('Date'[Date]
        )
    )
    3. P&L_YTD =
        CALCULATE([M_P&L],
        DATESYTD('Date'[Date]
        )
    )
    4. M_MTD_Previous_Year =
        CALCULATE([M_P&L],
            CALCULATETABLE(
                DATEADD('Date'[Date],-12,MONTH)
        )
    )
    5. Full Year Projection =
    VAR _ytd_pl=[P&L_YTD]
    VAR _mtd_pl=[P&L_MTD]
    VAR _mtd_prev_year=[M_MTD_Previous_Year]
    VAR _previousmonth=max(Sheet9[DatesFullYearProjection])
    VAR _Result=
    IF(
        ISBLANK(_mtd_pl),
            _ytd_pl+_mtd_prev_year,
        _ytd_pl
    )
    RETURN _Result
     

    I hope this is ok for you.

     

     

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi kristel_tulio  show me with your data what do you expect (wanted data amounts) so I can check that.

    PS I will be busy in the next days so maybe you will wait my respond (for days) 🙂

    • kristel_tulio's avatar
      kristel_tulio
      Helper III
      3 years ago

      Hi some_bih ,

      Thank you for helping! I would like to show the values based on the current year selected month instead of MTD. plus the previous year months that was not selected.

       

      If I selected the months this year January to May in the slicer, then it adds the preious years month that was not selected. And I want to show it in card visual