Comparing dates from two different tables

[Anonymous]

I have 2 tables, the first contains all the transactions(TransactionTable) and the second one contains all the dates (DateTable) from 1st Jan 2018 to 31st Dec 2021.

I have a expired date for each batch of my products stored in the inventory. I want to write a measure to calculate difference between expiry date and the date in the DateTable, to determine expired products. I have a seperate table for dates and the expiry date is in my transactions table. How to I compare dates from two different tables?

I want to plot a graph of Number of Expired products against the date. So I want to know on a certain date, how many products in stock will be expired. Store Transactions Table

[v-lionel-msft]

Hi @Anonymous ,

Shouldn't the expiration date of the product be compared with today?

__DateDiff = 
DATEDIFF(
    TODAY(),
    MAX(Sheet1[expiration_date]),
    DAY
)

__Number of expiration = 
CALCULATE(
    COUNT(Sheet1[Product_id]),
    FILTER(
        Sheet1,
        [__DateDiff] < 0 
    )
)

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[AllisonKennedy]

@Anonymous
I don't understand what you mean?

You need to have a way to relate/link the Date and the transaction table. If you want to plot the number of expired products against date, you just need to create a relationship between expiry date and the date in your date table.

Then put Month or date[date] and transaction[item] in the visual with summarization of item set to COUNT.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Date diff = datediff(min(Transacation[Expiry Date]), Max(Date[Date]),Day)

But you need to force a context using values or summarize

Refer for context

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

I have tried to elaborate the question.