cancel
Showing results for
Did you mean:
Helper III

## Count number of products over time

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

1 ACCEPTED SOLUTION
Community Support

Hi @Sasha,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
5 REPLIES 5
Resident Rockstar

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

Helper III

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
Community Support

Hi @Sasha,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper III

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

Helper III

Thanks! It exactly what I was looking for!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors