Forum Discussion

ZakyQadir's avatar
ZakyQadir
Frequent Visitor
2 years ago
Solved

Like for Like comparison

Hi, I need your help on this issue, I need to find Like For Like (YoY comparison) between two full period.  I have 3 table. Sales Table List Data Table Calendar Table I try to make comp...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution Dangar332  and Greg_Deckler  offered, and i want to offer some more infotmation for user to refer to.

    hello ZakyQadir , you can refer to the following sample.

    sample data is the same as you privded, i create a calendar table, and create relationship among tables.

    Create the following measures.

    Sales = SUM(Sales[Sales])
    StartofMonth =
VAR a =
    MAX ( 'List Data'[OpenDate] )
VAR b =
    EOMONTH ( a, -1 ) + 1
VAR c =
    EOMONTH ( a, 0 ) + 1
RETURN
    IF ( [Sales] <> BLANK (), IF ( b = a, b, c ) )

    TY LFL Sales =
VAR a =
    EDATE ( [StartofMonth], 12 )
VAR b =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        OR (
            YEAR ( b ) = YEAR ( [StartofMonth] )
                && b >= [StartofMonth],
            YEAR ( b ) = YEAR ( a )
                && b >= a
        ),
        [Sales]
    )

    LY LFL Sales = IF([TY LFL Sales]<>BLANK(),CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))
    % LFL = DIVIDE([TY LFL Sales]-[LY LFL Sales],[LY LFL Sales])

    Then put the measures to the visual.

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Output

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

ZakyQadir First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

  • ZakyQadir's avatar
    ZakyQadir
    Frequent Visitor
    2 years ago

    Yes, but my knowledge not that good, i need your help about that, my measure wrong maybe there's another solution from the experts

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      Thanks for the solution Dangar332  and Greg_Deckler  offered, and i want to offer some more infotmation for user to refer to.

      hello ZakyQadir , you can refer to the following sample.

      sample data is the same as you privded, i create a calendar table, and create relationship among tables.

      Create the following measures.

      Sales = SUM(Sales[Sales])
      StartofMonth =
VAR a =
    MAX ( 'List Data'[OpenDate] )
VAR b =
    EOMONTH ( a, -1 ) + 1
VAR c =
    EOMONTH ( a, 0 ) + 1
RETURN
    IF ( [Sales] <> BLANK (), IF ( b = a, b, c ) )

      TY LFL Sales =
VAR a =
    EDATE ( [StartofMonth], 12 )
VAR b =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        OR (
            YEAR ( b ) = YEAR ( [StartofMonth] )
                && b >= [StartofMonth],
            YEAR ( b ) = YEAR ( a )
                && b >= a
        ),
        [Sales]
    )

      LY LFL Sales = IF([TY LFL Sales]<>BLANK(),CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date])))
      % LFL = DIVIDE([TY LFL Sales]-[LY LFL Sales],[LY LFL Sales])

      Then put the measures to the visual.

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      Output

       

       

      • ZakyQadir's avatar
        ZakyQadir
        Frequent Visitor
        2 years ago
        It works really well, thanks a lot for your support...