Forum Discussion

gwaczoka's avatar
gwaczoka
Regular Visitor
3 years ago

New column with value from previous (last) date

Hi guys,

can someone please help me with this issue:

I would like to have in another column product flag from previous date (not DAY-1, but last previous) 

 

I've found a solution for similar problem:

Previous Day Sales = 

CALCULATE(
    SUM(Table2[Sales]),
    LASTDATE(
    FILTER(
        ALL(Table2[Date] ),
        Table2[Date] < EARLIER([Date]) 
        )
    ), 
    ALLEXCEPT(Table2,Table2[Store ID])
)

 

 

but I have two variables: ID and vendor

 

Can you please help me to achieve a solution like in Column E?

 

DateIDVendorProduct flag
19.09.20221A1
19.09.20221B0
19.09.20221C0
20.09.20222A0
20.09.20222B1
20.09.20222C1
21.09.20223A1
21.09.20223B1
21.09.20223C1
22.09.20224A1
22.09.20224B0
22.09.20224C1
Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    gwaczoka Try:

    Column = 
    VAR __Date = [Date]
    VAR __Vendor = [Vendor]
    VAR __Last = MAXX(FILTER('Table2',[Date] < __Date && [Vendor] = __Vendor),[Date])
    VAR __Result = MAXX(FILTER('Table2',[Date] = __Last && [Vendor] = __Vendor),[Product flag])
RETURN
    __Result
    • gwaczoka's avatar
      gwaczoka
      Regular Visitor
      3 years ago

      Hi Greg,

      thank you for the response and  sorry for late reply.

      It doesn't work, because there is no information about ID of the product included 😞
      It is in the second column, might looks like hidden

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi gwaczoka,

        Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

        How to Get Your Question Answered Quickly  

        Regards,

        Xiaoxin Sheng