Sum of diference between the MaxDate and MinDate with each Product, excluding who appears once time

[Rafael_Almeida]

Hi everyone!

I posted yesterday a question and @CerebusBI could help me with a solution : https://community.powerbi.com/t5/DAX-Commands-and-Tips/Count-distinct-values-on-a-column-for-values-...

This measures from my questioins are part of a major measure to calculate MTBF (Mean Time Between Fails) with data i have.

So, another question...

I have to calculate a sum of diference between the Maximum date and Minimum date with each "Name of Product", excluding the Products who appears once time, so the example table is:

Name of Product	Open date fail
Product 1	01/25/2021
Product 2	03/15/2021
Product 2	03/20/2021
Product 1	01/15/2021
Product 3	04/02/2021
Product 1	12/08/2021
Product 1	10/13/2021

Being the maximum date locked, because i have to get always the last Thursday (refreshed automatically everyday when i publish repport):
MaxDate  = 

IF(WEEKDAY(TODAY(),1)=1,(TODAY()-3),

IF(WEEKDAY(TODAY(),1)=2,(TODAY()-4),

IF(WEEKDAY(TODAY(),1)=3,(TODAY()-5),

IF(WEEKDAY(TODAY(),1)=4,(TODAY()-6),

IF(WEEKDAY(TODAY(),1)=5,TODAY(),

IF(WEEKDAY(TODAY(),1)=6,(TODAY()-8),

IF(WEEKDAY(TODAY(),1)=7,(TODAY()-9))))))))

And the minimum date must be the MinDate of each Product, except the products that only appear once:

The measure i use until now:

MinDATE =

CALCULATE(

MIN(Products[Name of Product]),

VALUES(Products[Name of Product]))

Name of Product	MinDate
Product 1	01/15/2021
Product 2	03/15/2021

So, the Measure who i need results in:

Sum of (MaxDate - MinDate) excluding the Products who appears once time. (I must use a Card graphic and Table Graphic)

Can anyone help me with this?




Thank to the whole community, for everything!

[ValtteriN · Accepted solution]

Hi,

You can achieve this using DATEDIFF, SUMX and some filters.
e.g.
Data:

Dax:

Products datediff =

var _sdate = CALCULATE(min(Products[Date]),ALLEXCEPT(Products,Products[Product]))

var _edate = CALCULATE(max(Products[Date]),ALLEXCEPT(Products,Products[Product]))

return

DATEDIFF(_sdate,_edate,DAY)

SUMX of products datediff =

Var vartable = GROUPBY(Products,Products[Product]) return

SUMX(vartable,[Products datediff])

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!