Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Difference between columns with condition referring to another column

Jump to Best Answer
  • 1.  Difference between columns with condition referring to another column

    Posted 28 days ago
    Hello
    Please help me with a DAX measure where i can calculate the values as described.
    Based on column "Service type", i want to make the difference between END DATE and INITIAL DATE, or END DATE - INTERMEDIARY DATE OR INTERMEDIARY DATE - INITIAL DATE and have the result into one column "Difference"
     = IF SERVICE TYPE is AAA1  than END DATE -INITIAL DATE && IF SERVICE TYPE = AAB4 than END DATE - INTERMEDIARY DATE...
    Thank you

    INITIAL DATE INTERMEDIARY DATE END DATE SERVICE TYPE Difference
    1/1/2022 3/4/2022 6/6/2022 AAA1  
    1/12/2022 4/15/2022 6/8/2022 AAB4  
    1/23/2022 4/27/2022 6/10/2022 ACD4  
    2/3/2022 3/4/2022 6/12/2022 ADD9  
    2/14/2022 4/15/2022 6/14/2022 AAA1  
    2/25/2022 4/27/2022 6/16/2022 AAB4  
    3/8/2022 3/4/2022 6/18/2022 ACD4  
    3/19/2022 4/15/2022 6/20/2022 ADD9  
    3/30/2022 4/27/2022 6/22/2022 AAA1  
    4/10/2022 3/4/2022 6/24/2022 AAB4  
    4/21/2022 4/15/2022 6/26/2022 ACD4  
    5/2/2022 4/27/2022 6/28/2022 ADD9  


    ------------------------------
    Mihai Stanca
    Acasa
    ------------------------------


  • 2.  RE: Difference between columns with condition referring to another column
    Best Answer

    Posted 18 days ago
      |   view attached
    @Mihai Stanca,
    If this is the only table you have, then I you just need to add a calculated column like this:
    Difference = 
        SWITCH(
            Table1[ServiceType],
            "AAA1", DATEDIFF(Table1[InitialDate], Table1[EndDate], DAY),
            "AAB4", DATEDIFF(Table1[IntermediaryDate], Table1[EndDate], DAY),
            DATEDIFF(Table1[InitialDate], Table1[IntermediaryDate], DAY)
        )​
    ​And when you place that into a table visual, you get this:


    Hope that helps.

    Kaz.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    StancaExample_v1.pbix   64 KB 1 version