Forum Discussion

KC_user's avatar
KC_user
Regular Visitor
4 years ago

Filtering twice the same field at different dates

Hello everyone , 

New user here so don't hesitate to tell me if I'm doing something wrong.

 

So I've got two tables that are a Calendar and a Sales table.

Calendar is like so : 

DateSame_day_another_year
27/04/2022  29/04/2020
26/04/2022  28/04/2020

Where same_day_another_year is the same day of the same week but another year that i can filter.

 

Sales is like so :

DateShop_numberShop_openturnover
27/04/20221110
27/04/20222130
…………
29/04/20201130
29/04/20202

0

90

Where I have the sum of turnover for each shop each day and an indicator of if the shop was open

Sales and Calendar are linked on the date.

 

Now,  I am trying to get the total turnover of each day and its equivalent another year ONLY for the shops that were open on both dates.

For exemple here that would give me :

DateSame_day_another_yearSUM(turnover) 2022SUM(turnover) 2020
27/04/2022   29/04/20201030

Because only the shop 1 was open at Date and Same_day_another_year 

I have tried a lot of things but none seemed to work.

Does anyone have an idea on how I could proceed ?

 

Thanks in advance !

Need Help

4 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    Hi KC_user ,

     

    You can create more than one relationship between two tables - one is an active relationship and the others are inactive ones which can be invoked by using USERELATIONSHIP function. Try this measure below:

    Turnover - same day another year =
CALCULATE (
    SUM ( Sales[turnover] ),
    USERELATIONSHIP ( 'Calendar'[Same_day_another_year], Sales[Date] )
)

     

    This is how the two tables relate to each other:

    Active Relationship - Calendar[Date] > Sales[Date]
    Inactive Relationship - Calendar[Same_day_another_year] > Sales[Date]

  • KC_user's avatar
    KC_user
    Regular Visitor
    4 years ago

    Thank you for your answer, I didn't know about that !
    However I have no problem calculating the turnover for the previous date.

    My true problem is calculating the turnover ONLY if the shop is open at the recent date AND at the previous date.

    • danextian's avatar
      danextian
      Super User
      4 years ago

      Hi KC_user ,

      Have you tried using the approach in my previous post? This should return the same result as this:

       

       

      • KC_user's avatar
        KC_user
        Regular Visitor
        4 years ago

        Hello danextian , 

        Thanks again for the answer. 
        I understand your answer, however what you display gives the sum of turnover for each shop.
        Where as i need the total turnover of the day only including shops that are were open on both dates. 

        Let me give you another more detailed example :

        If my sales table is like so :

        DateShop_numberShop_open turnover
        27/04/20221110
        27/04/20222130
        27/04/20223040
        27/04/20224120
        …   
        29/04/20201150
        29/04/20202015
        29/04/20203125
        29/04/20204160


        Then i want the result to be :

        DateSame_day_another_yearTurnover 2022Turnover 2020
        27/04/202229/04/202030110

         

        Thanks !