Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous Month Result - New Column

Hi guys,

 

I'm having problem with my calculation, I'm not sure why I can't get a result of my previous month in current month row:

 

for example 

I would like to show Previous Month Result for May, which should be 90 (April Result).

I've tried many different scenarios and calculations, but nothing seems to be working...

I feel like this simple code should work, but it doesnt:
PrevMonthValue = CALCULATE(SUM('KPI for Power BI'[Result]), PREVIOUSMONTH('KPI for Power BI'[Date]))

can someone help, please?

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago
    PrevMonthValue =
    VAR CurrentRank = RANKX(ALL('KPI for Power BI'), 'KPI for Power BI'[Date],, ASC, DENSE)
    RETURN
        CALCULATE(
            SUM('KPI for Power BI'[Result]),
            FILTER(
                ALL('KPI for Power BI'),
                RANKX(ALL('KPI for Power BI'), 'KPI for Power BI'[Date],, ASC, DENSE) = CurrentRank - 1
                    && 'KPI for Power BI'[Title] = EARLIER('KPI for Power BI'[Title])
            )
        )
    this one actually worked in my case, but thank you for all your help - if somebody will have similar issue it might be useful though 🙂

10 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this

    PrevMonthValue = 
VAR _RX =
    RANKX ( 'KPI for Power BI', [Date],, ASC, DENSE ) - 1
RETURN
    CALCULATE (
        SUM ( 'KPI for Power BI'[Result] ),
        FILTER (
            ALL ( 'KPI for Power BI' ),
            RANKX ( 'KPI for Power BI', [Date],, ASC, DENSE ) = _RX
        )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ahmed,

       

      Thank you for your answer, it kinda works now, but it probably gives me sum of all results (?) instead of specific row value:

       

      could you please help with that too?

       

      thank you in advance

      • Ahmedx's avatar
        Ahmedx
        Super User
        3 years ago

        Share sample pbix file to help you.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    or

    PrevMonthValue = 
VAR _PriviosMonth =
    EOMONTH([Date],-2)+1
RETURN
    CALCULATE (
        SUM ( 'KPI for Power BI'[Result] ),
        FILTER (
            ALL ( 'KPI for Power BI' ),
            [Date]=_PriviosMonth
        )
    )

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    or

    PrevMonthValue = 
VAR _PriviosMonth =
    PREVIOUSMONTH('KPI for Power BI'[Date])
RETURN
    CALCULATE (
        SUM ( 'KPI for Power BI'[Result] ),
        FILTER (
            ALL ( 'KPI for Power BI' ),
            [Date]=_PriviosMonth
        )
    )

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    or

    PrevMonthValue = 
    CALCULATE (
        SUM ( 'KPI for Power BI'[Result] ),ALL('KPI for Power BI'),
        OFFSET(-1, ALL('KPI for Power BI'[Date]),ORDERBY('KPI for Power BI'[Date])))

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    first you need to sort the date in ascending order and then do everything that was shown in my video
    see my video:

    https://1drv.ms/v/s!AiUZ0Ws7G26RiTduABSF0IwK-laz?e=RBe3sb

    PrevMonthValue = 
    CALCULATE (
        SUM ( 'KPI for Power BI'[Result] ),ALL('KPI for Power BI'),
        OFFSET(-1, 
             SUMMARIZE('KPI for Power BI','KPI for Power BI'[Date],'KPI for Power BI'[year]),
                   ORDERBY('KPI for Power BI'[Date]),,PARTITIONBY('KPI for Power BI'[year])
        
        ))