Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Current year cumulative with forecast & last year cumulative

  • 1.  Current year cumulative with forecast & last year cumulative

    Gold Contributor
    Posted Jun 26, 2022 04:35 AM
    I am trying to achieve the same cumulative result as I mentioned in attached snapshot.
    Sample file is also attached. Please calculate a measures and send back to me.

    Thank you


    ------------------------------
    Khurram Muhammad
    Data Analyst
    Karachi
    ------------------------------

    Attachment(s)

    pbix
    cumulative example.pbix   83 KB 1 version
    xlsx
    Cumulative example.xlsx   14 KB 1 version


  • 2.  RE: Current year cumulative with forecast & last year cumulative

    Top Contributor
    Posted Jun 26, 2022 06:30 PM
    Edited by Nicolas MENDEZ Jun 26, 2022 06:31 PM
    Hello,

    Here is my solution

    To get the result I wrote 3 measures :

    YTD TY = 
    VAR _Year =  MAXX( ALL('Calendar Table'[Year]), 'Calendar Table'[Year]) 
    RETURN
    CALCULATE(
        [YTD], 
        'Calendar Table'[Year] = _Year 
    )​

    YTD LY = 
    VAR _Year =  MAXX( ALL('Calendar Table'[Year]), 'Calendar Table'[Year]) - 1 
    RETURN
    CALCULATE(
        [YTD], 
        'Calendar Table'[Year] = _Year 
    )​
    20 % Forecast YTD = 
    VAR _CurrMonth = SELECTEDVALUE( 'Calendar Table'[MonthOfYear] )
    VAR _LastMonth = MONTH( LASTNONBLANK( ALL('Calendar Table'[Date]), [Total Budget] ) )
    VAR _MaxYTDCurrYear = CALCULATE( [YTD], 'Calendar Table'[MonthOfYear] = _LastMonth ) 
    VAR _MaxYTDLastYear = CALCULATE( [YTD LY], 'Calendar Table'[MonthOfYear] = _LastMonth)
    VAR _YTDLastYear = 
        CALCULATE( 
            [YTD LY],
            FILTER( 
                ALL('Calendar Table'[MonthOfYear]), 
                'Calendar Table'[MonthOfYear] > _LastMonth && 'Calendar Table'[MonthOfYear] <= _CurrMonth
            )
        )
    RETURN
    
    _MaxYTDCurrYear + 
    IF( 
        _CurrMonth > _LastMonth , 
        _YTDLastYear - _MaxYTDLastYear
    ) * 0.8


    Should work ... I hope this is a good start for you....



    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------