cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joshlukeharris
New Member

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: 

Joshlukeharris_3-1634633720517.png

 

 

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

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

Incidentally Alberto has just talked about that in his new video

 

Checking Boolean conditions while creating a like for like comparison in DAX – Unplugged #36 - SQLBI

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. 

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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors