Forum Discussion

ChristianDGreat's avatar
ChristianDGreat
Icon for Resolver I rankResolver I
3 years ago
Solved

Get last year last entry

I got stuck into this, hopefully, someone can help me with this
So I have this Custom Calendar Table

SeasonWeekendingProductSub Product
202110/29/2022AA-1
202111/5/2022AA-1
202211/12/2022AA-1
202211/19/2022AA-1
20218/27/2022AA-2
20219/3/2022AA-2
20229/10/2022AA-2
20229/17/2022AA-2
20229/24/2022AA-2


and transacation table :

SeasonWeekendingProductSub ProductQty
20219/3/2022AA-2111
202110/29/2022AA-1222
202211/5/2022AA-233
202211/12/2022AA-244
202211/19/2022AA-155



The result Im looking for is something like this

Filter :
Season = 2022,
Product = A,
Sub Product = All

ProductSub ProductQty
AA-10
AA-2111
Total 111

 

Explanation :


Basically what this does is if a user select Season = 2022, it need to scan the Custom Calendar on the MIN date available for 
Product, Season and Sub Product combination, in this case in row 1, its 11/12/2022, then we need to subtract 7 there
so the date will be 11/5/2022. Now we need to look at the transaction table if we have those match for the 2021 Season.
In this case theres none so its 0

For Row 2 result, the minimum date for the Custom Calendar is 9/10/2022, minus 7 thats 9/3/2022,
When we lookup the transaction table for 9/3/2022 and 2021 we get 111

 

Hope this makes sense.

*basically we are looking at the last weekending of the previous year.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

    Expected result measure: =
VAR _mindateandoneweekbefore =
    MIN ( 'Calendar'[Weekending] ) - 7
RETURN
    SUMX (
        FILTER (
            'Transaction',
            'Transaction'[Weekending] = _mindateandoneweekbefore
                && 'Transaction'[Product] = MAX ( 'Calendar'[Product] )
                && 'Transaction'[Sub Product] = MAX ( 'Calendar'[Sub Product] )
        ),
        'Transaction'[Qty]
    ) + 0

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

    Expected result measure: =
VAR _mindateandoneweekbefore =
    MIN ( 'Calendar'[Weekending] ) - 7
RETURN
    SUMX (
        FILTER (
            'Transaction',
            'Transaction'[Weekending] = _mindateandoneweekbefore
                && 'Transaction'[Product] = MAX ( 'Calendar'[Product] )
                && 'Transaction'[Sub Product] = MAX ( 'Calendar'[Sub Product] )
        ),
        'Transaction'[Qty]
    ) + 0