Forum Discussion

Joshlukeharris's avatar
Joshlukeharris
Regular Visitor
4 years ago

Like for Like calc based on a single data set across multiple time periods. adding calc table

Hi, 


I am new to this community and struggling with a time period like for like calc. Please Help 🙏

I have a data set with various information, including a single date column, column for customer code (stat dimension PD EMEA) and column for quantity.

I need a solution for comparing like for like across multiple time periods. To start with year 2021 vs 2020. 

 

My current solution comes close to answering my question, showing sold / not sold in 2020 but for 2021 it shows "sold" or "blank". This causes an error as it only filters out the customer code not sold in 2020 rather than giving me just the customer codes sold in both years. 

See below calcualted table formula and example of the issue. 
 

Calculated table: 

PD EMEA Status =
VAR AllPDEMEA =
CROSSJOIN (
FILTER (
ALLNOBLANKROW ( 'PP_TEST_FOR_JOSH_FLAT_DATA'[Date].[Year]),
'PP_TEST_FOR_JOSH_FLAT_DATA'[Date].[Year] IN { 2019,2020}
),
ALLNOBLANKROW ( PP_TEST_FOR_JOSH_FLAT_DATA[Stat Dimension PD EMEA] )
)
VAR SoldPDEMEA =
SUMMARIZE (
PP_TEST_FOR_JOSH_FLAT_DATA,
'PP_TEST_FOR_JOSH_FLAT_DATA'[Date].[Year],
PP_TEST_FOR_JOSH_FLAT_DATA[Stat Dimension PD EMEA]
)
VAR Result =
UNION(
ADDCOLUMNS ( SoldPDEMEA, "Status", "Sold" ),
ADDCOLUMNS (EXCEPT( AllPDEMEA, SoldPDEMEA), "Status", "Not Sold" )
)
RETURN
Result

 

Outcome: 

 

 

As you can see the value in 2021 is blank because the "Values" pin is only showing the "first status" rather than status throughout the data set. 

Once I have figured out how to fix this issue for Year 2020 vs Year 2021 Like For Like, I would then like to add in a way of selecting different time periods to compare. Example time periods would be YTD vs YTD PY, Month vs PY, Month vs Month PY etc. 

 

I am excited to see if anyone is able to help  🙏

Thanks, 
Josh

 

Need Help
Tutorial Requests

4 Replies

    • Joshlukeharris's avatar
      Joshlukeharris
      Regular Visitor
      4 years ago

      Hi lbendlin Thank you so much for coming back to me.
      Yes i'd watched this video a couple weeks back as this was the solution i used as my first attempt.
      The issue here though is that it only looks one way. By this i mean it only looks at the first year and then if values are done in the following year.
      I need a solution that only considers customer codes with values shown in both time periods. 

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        Watch the video again. It goes deep into the intricacies of properly identifying like for like data ( well, minus the weekday issue).

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Joshlukeharris,

    Any update for this? Did lbendlin's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng