Forum Discussion

Tani4ka's avatar
Tani4ka
Icon for Helper II rankHelper II
1 year ago
Solved

Previous Period measure

hello,

I need to create a measure that will show comparison for a previous period when the data is monthly/quarterly of Yearly.

 

So far i created one that gives correct compariosn MoM but when 2 or more are selected the resutls are completely off as I used PREVIOUSMONTH.  is there any function that can do PREVIOUSPERIOD?

 

My measure: 

JA Sent PM =
VAR sentActual =
    CALCULATE(
        SUM(Raw_data[JA sent])
    )

VAR sentLY =
    CALCULATE(
        SUM(Raw_data[JA sent]),
        PREVIOUSMONTH(dim_time[Date])
    )

RETURN
    IF(
        ISBLANK(sentLY) || ISBLANK(sentActual) || sentLY = 0 || sentActual = 0,
        BLANK(),
        DIVIDE(sentActual - sentLY, sentLY))
 
Many thanks
Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    Tani4ka , Previous month take first avaiable month. So your Qtr and yeat comparison will not work. 
    Do Switch the measure using isinscope 

    How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

    Switch(true(),
    isinscope(Date[Month]) , [MoM%],
    isinscope(Date[Qrt]) , [QOQ%],
    isinscope(Date[Year]) , [YoY%])


    You can have full formula, if you do have all measures 

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    1 year ago

    Tani4ka , Previous month take first avaiable month. So your Qtr and yeat comparison will not work. 
    Do Switch the measure using isinscope 

    How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA

    Switch(true(),
    isinscope(Date[Month]) , [MoM%],
    isinscope(Date[Qrt]) , [QOQ%],
    isinscope(Date[Year]) , [YoY%])


    You can have full formula, if you do have all measures 

  • Tani4ka's avatar
    Tani4ka
    Icon for Helper II rankHelper II
    1 year ago

    thank you for your responce but I need a bit more clarity on how to use it.

     

    I've update the measure but is gives rather 0 results 

     

    PM_created_NGT = SWITCH (
        TRUE (),
        ISINSCOPE ( 'dim_time'[Month] ), [JA Created PM],
        ISINSCOPE ( 'dim_time'[Quarter]), [JA Created PQ],
        ISINSCOPE ( 'dim_time'[Year] ), [JA Created PY]
       
    )
     

     

    JA Created PY =
    VAR createdActual =
        CALCULATE(
            SUM(Raw_data[JA created])
        )

    VAR createdLY =
        CALCULATE(
            SUM(Raw_data[JA created]),
            PREVIOUSYEAR(dim_time[date])
        )

    RETURN
        IF(
            ISBLANK(createdLY) || ISBLANK(createdActual) || createdLY = 0 || createdActual = 0,
            BLANK(),
            DIVIDE(createdActual - createdLY, createdLY))
     
     
    JA Created PY returns correct resutls for the total YoY but the rest of the lines are wrong. 
     
    Can you kindly point me to what I am doing wrong?
     
    Many thanks
    • Tani4ka's avatar
      Tani4ka
      Icon for Helper II rankHelper II
      1 year ago

      I realised were the problem was - i used incorred date columns. 

      The measure now works well but when i add a custom date columns to sort the data correctly i get 0 resutls. It only react to the main date column and the order is not correct. I tried updating the date columns in the measure to the cutom created but no luck. Any suggestions? 

       

      • Tani4ka's avatar
        Tani4ka
        Icon for Helper II rankHelper II
        1 year ago

        This is not sorted - i need to remove auto date/time tick box and swith to custom date columns. All works perfectly now. Thanks a million for your help!