Forum Discussion

Tani4ka's avatar
Tani4ka
Helper 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 ...
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 

Tani4ka's avatar
Tani4ka
Helper 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
Helper 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
    Helper 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!