Date diff on category: I don't want that

[Hank_]

Hello everybody,

I'd like to calculate the difference between 2 specific dates for every product, and then the KPIs based on this date.

I already made it with calculated columns, but I'd like to manage it with DAX (I'm new to DAX).

I calculate the date difference:

date_diff = DATEDIFF(FIRSTNONBLANK(table[date1],""),FIRSTNONBLANK(table[date2],""),DAY)

 and then I calc the KPI as:

KPI = IF([date_diff]<30,"OK","KO")

This works and my output is:

what          days   KPI   date 1 date 2
Product 1   20d   OK   1 jan    20 jan
Product 2   40d   KO   1 jan     9 feb
Product 3   10d   OK   1 feb   20 feb
... ... ...    

The issue arises when I add to my table category above the product. I get:

what            days  KPI   date 1 date 2
CATEG. A     51d   KO    1 jan   20 feb
  Product 1   20d   OK    1 jan  20 jan
  Product 2   40d   KO    1 jan    9 feb
  Product 3   10d   OK    1 feb  20 feb
CATEG. B ... ...    
  Product 4 ... ...    

Category gets the date calculated in the same way as products do.

So if computes the difference between the first non-blank dates in all the products of its category. This is not what I'd like to obtain.

I'd like to have:

what          days   KPI   date 1 date 2
CATEG. A        
  Product 1   20d   OK   1 jan   20 jan
  Product 2   40d   KO   1 jan     9 feb
  Product 3   10d   OK   1 feb  20 feb

Or, the best would be:

what           days      KPI     date 1 date 2
CATEG. A    23,3d   66,6%   
  Product 1   20d      OK     1 jan   20 jan
  Product 2   40d      KO     1 jan     9 feb
  Product 3   10d      OK     1 feb  20 feb

Since I'm a noob with DAX, maybe my approach is incorrect?

PS: Sorry for the tables but I cannot post due to invalid HTML... 

[v-eqin-msft · Accepted solution]

Hi @Hank_ ,

According to my understand, you want to show average of data diff  and the percentage of  "OK" for each Category, right?

You could follow these formulas:

1. For date diff

DateDiff =
DATEDIFF ( MAX ( 'Table'[Date Start] ), MAX ( 'Table'[Date End] ), DAY )

days =
FORMAT (
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        [DateDiff],
        AVERAGEX ( 'Table', [DateDiff] )
    ),
    "#.0d"
)

2. For KPI:

KPI Value =
IF ( [DateDiff] < 30, "OK", "KO" )

KPI =
VAR _per =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Category] ), [KPI Value] = "OK" )
    )
        / CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
    IF ( HASONEVALUE ( 'Table'[Product] ), [KPI Value], _per )

My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Hank_]

THANK YOU! It's definitely the output I wanted.

There's so much stuff to learn for me in this tiny pbix 😃 

[Hank_]

Thank you @amitchandak|! Very cool IsInScope, didn't discovered it yet 🙂

I this ways I can "hide" values for CATEGORY. If I would calculate for them the actual average days and KPI %? 

[amitchandak]

@Hank_ , if what to change the calculation based on the level, you need to use isinscope. But looking at this calculation, I am not able to make, where is a column used, where is measure is used 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/