Forum Discussion

Oros's avatar
Oros
Post Prodigy
1 year ago
Solved

Identify latest date from another table

Hello,
 
I have 2 tables (count table and inventory table).
 
Products in the count table appears multiple times because they are counted based on location.  The same product could be in different locations.
 
Products in inventory table appear only once (unique) because this table hold the total inventory for each product.
 

 

What would be the correct measure or column if I would like to compare the 2 tables so based only on today's date as the value in the LAST COUNTED ON column?
 
If today's date is October 17, 2024, then the resulting table must be as below.  
Please see sample pbix (if needed).  Thanks!
 
 
 
Need Help
  • audreygerred's avatar
    audreygerred
    1 year ago

    Hello! I created the below measure and it works as expected:

    Counted =
    IF(
        MAX('YourTable'[LastCountedOn]) = TODAY(),
        "Yes",
        "No"
    )
     
    Here is the sample data I used:

    Here is the output:

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Oros ,

     

    Try the following dax expression. The reason it differs from the expected result you show is because today is the 18th of October.

    NOT COUNTED = 
VAR _max_date =
    CALCULATE (
        MAX ( 'COUNT TABLE'[LAST COUNTED ON] ),
        FILTER ( 'COUNT TABLE', 'COUNT TABLE'[PRODUCT] = 'INVENTORY TABLE'[Product] )
    )
RETURN
    IF (
        FORMAT ( _max_date, "yyyy-mm-dd" ) = FORMAT ( TODAY (), "yyyy-mm-dd" ),
        "Yes",
        "No"
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

4 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    1 year ago

    Hello! I created the below measure and it works as expected:

    Counted =
    IF(
        MAX('YourTable'[LastCountedOn]) = TODAY(),
        "Yes",
        "No"
    )
     
    Here is the sample data I used:

    Here is the output:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Oros ,

     

    Try the following dax expression. The reason it differs from the expected result you show is because today is the 18th of October.

    NOT COUNTED = 
VAR _max_date =
    CALCULATE (
        MAX ( 'COUNT TABLE'[LAST COUNTED ON] ),
        FILTER ( 'COUNT TABLE', 'COUNT TABLE'[PRODUCT] = 'INVENTORY TABLE'[Product] )
    )
RETURN
    IF (
        FORMAT ( _max_date, "yyyy-mm-dd" ) = FORMAT ( TODAY (), "yyyy-mm-dd" ),
        "Yes",
        "No"
    )

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

    • Oros's avatar
      Oros
      Post Prodigy
      1 year ago

      Hi Anonymous,

       

      It works!  Thanks.