Forum Discussion

sjoshi's avatar
sjoshi
Icon for Helper II rankHelper II
5 years ago
Solved

How to achieve difference between min/max based on certain column

 

Hello

Fact table in EDW contains everday snapshot of certain orders as below and we need to calculate various derived aging as shown in second set of below screenshot. Power BI should show only current day record and also should show dervied status bucket 

 

 

Thanks

Smita

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi sjoshi ,

     

    Based on your description, you can create a calculated table as follows.

     

    Sample =

    var x1=MAX(Table4[snp_date])

    var x2=SUMMARIZE(FILTER('Table4',[snp_date]=x1),Table4[Order Number],Table4[Derived Status],Table4[snp_date])

    return

    ADDCOLUMNS(

        x2,

        "Not Retuned",COUNTROWS(FILTER('Table4',[Derived Status]="Not Retuned"))-1&" day",

        "Service In-transit Aging",COUNTROWS(FILTER('Table4',[Derived Status]="Service In-transit"))-1&" day",

        "At 3PL warehouse Aging",COUNTROWS(FILTER('Table4',[Derived Status]="At 3PL warehouse"))-1&" day",

        "warehouse in-transit Aging",COUNTROWS(FILTER('Table4',[Derived Status]="warehouse in-transit"))-1&" day"

        )

     

    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

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

2 Replies

  • sanalytics's avatar
    sanalytics
    Icon for Super User rankSuper User
    5 years ago

    sjoshi 

     

    Create 2 measures and 1 disconnected table in order to achive this.

     

    Please find the below link for power bi file

    https://dropfiles.org/ELJwx1v0

    Hope it helps

    Regards

    sanalytics

    if it is your solution please like and please accept it as solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi sjoshi ,

     

    Based on your description, you can create a calculated table as follows.

     

    Sample =

    var x1=MAX(Table4[snp_date])

    var x2=SUMMARIZE(FILTER('Table4',[snp_date]=x1),Table4[Order Number],Table4[Derived Status],Table4[snp_date])

    return

    ADDCOLUMNS(

        x2,

        "Not Retuned",COUNTROWS(FILTER('Table4',[Derived Status]="Not Retuned"))-1&" day",

        "Service In-transit Aging",COUNTROWS(FILTER('Table4',[Derived Status]="Service In-transit"))-1&" day",

        "At 3PL warehouse Aging",COUNTROWS(FILTER('Table4',[Derived Status]="At 3PL warehouse"))-1&" day",

        "warehouse in-transit Aging",COUNTROWS(FILTER('Table4',[Derived Status]="warehouse in-transit"))-1&" day"

        )

     

    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

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