cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sasha
Helper III
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

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.

 

Capture.PNG

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.

View solution in original post

5 REPLIES 5
rajendran
Resident Rockstar
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

Hi,

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

Sample

 

Products Result expected
     
ProductExpiration date DateNot expired products
A31/12/2017 01/11/20173
B13/06/2018 01/12/20173
C02/07/2018 01/01/20182
   01/02/20182
   01/03/20182
   01/04/20182
   01/05/20182
   01/06/20182
   01/07/20181
   01/08/20180

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.

 

Capture.PNG

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.

View solution in original post

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

Thanks! It exactly what I was looking for!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors