cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Get the last loading date and total quantity per material

Nice day

I have a database in which material is continuously added as the days go by, I want to have a table that shows me the material, the last date on which the material is loaded, how many days have passed since then and the count of the total material that has been loaded, for example:

Database:

No partCreation DateQuantity
113/06/20223
116/06/20222
206/11/20221
206/12/20223
215/06/20224
306/11/20223
306/11/20221
306/12/20222

What I would like to show in the table would be the following taking into account that "Today" is 16/06/2022:

No partLast Creation Datetotal quantityCounting of days
116/06/202250
215/06/202281
306/12/202264

There are still many things that I need to learn with Dax so I would appreciate your support with this problem

1 ACCEPTED SOLUTION

Hi @FalcoTM ,

 

Try to create measures like so:

Measure - Total Quantity = 
CALCULATE ( SUM ( 'Table'[Quantity] ), ALLEXCEPT ( 'Table', 'Table'[No part] ) )
Measure - Day Counting = 
VAR Today_ =
    DATE ( 2022, 6, 16 )
RETURN
    DATEDIFF ( MAX ( 'Table'[Last Date] ), Today_, DAY )

Icey_0-1655791731044.png

 

 

Best Regards,

Icey

 

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

View solution in original post

3 REPLIES 3
mohammedadnant
Impactful Individual
Impactful Individual

Hi @Syndicate_Admin 

 

Try the following

SumQNty = Sum(MaterialLastDate[Quantity])

Max_Load_Date = max(MaterialLastDate[Creation Date])

DayDiff = DATEDIFF([Max_Load_Date],TODAY(),DAY)-1

Thanks & Regards,

Mohammed Adnan

Lean Power BI: https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

At the time of entering the formulas it gives me as a result the total sum of all the part numbers as well as the last date obtained, in this way

No partCreation DateQuantityLast DateTotal QuantityDay Counting
113/06/2022316/06/2022190
116/06/2022216/06/2022190
206/11/2022116/06/2022190
206/12/2022316/06/2022190
215/06/2022416/06/2022190
306/11/2022316/06/2022190
306/11/2022116/06/2022190
306/12/2022216/06/2022190

I would like the formula to get me the calculation for each part number in this way

No partCreation DateQuantityLast DateTotal QuantityDay Counting
113/06/2022316/06/202250
116/06/2022216/06/202250
206/11/2022115/06/2022 81
206/12/2022315/06/2022 81
215/06/2022415/06/2022 81
306/11/2022306/12/202264
306/11/2022106/12/202264
306/12/2022206/12/202264

On the other hand the formula of the counting of days works correctly

Hi @FalcoTM ,

 

Try to create measures like so:

Measure - Total Quantity = 
CALCULATE ( SUM ( 'Table'[Quantity] ), ALLEXCEPT ( 'Table', 'Table'[No part] ) )
Measure - Day Counting = 
VAR Today_ =
    DATE ( 2022, 6, 16 )
RETURN
    DATEDIFF ( MAX ( 'Table'[Last Date] ), Today_, DAY )

Icey_0-1655791731044.png

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors