Count number of products over time

[Sasha]

Hi,

I have a table of Products with each having an expiaration date.

Also, I have a table of Time dimension.

How can I define a measure counting all the products that are not expired over time?

Also, how can I present it on a graph showing only the 1st of each month using time dimension?

Thank you,

Alex

[Anonymous]

Hi

How the relationship is defined here? Which column is connected to Time dimension?

When you say "ounting all the products that are not expired over time" - is it for any specific date or period?

Would be easy to help you if you post some sample data and expected result in copy - pasteable format.

Thanks
Raj

[Sasha]

Hi,

the relationship is between time dimension and expiration dates on the products table

Sample

Products		Result expected
Product	Expiration date		Date	Not expired products
A	31/12/2017		01/11/2017	3
B	13/06/2018		01/12/2017	3
C	02/07/2018		01/01/2018	2
			01/02/2018	2
			01/03/2018	2
			01/04/2018	2
			01/05/2018	2
			01/06/2018	2
			01/07/2018	1
			01/08/2018	0

[v-frfei-msft · Accepted solution]

Hi @Sasha,

I made one sample based on your data.

1. Enter the data and create calculated table.

Table = CROSSJOIN('date',Table1)

2. Create a visual as below.

Also please find the pbix as attached.

Regards,

Frank

[Sasha]

Further drill down:

what if I want to turn this table into stacked chart area.

I would like then press on a month on the chart and get a table of all the products' names that are not expired at that point

It works with pressing on a table, it doesn't work when pressing on the chart (the table of products remains the same)

Thanks 

Alex

[Sasha]

Thanks! It exactly what I was looking for!