Forum Discussion

ankurdsharma's avatar
ankurdsharma
Frequent Visitor
7 years ago
Solved

DAX Help

Hi,   I am  new to Power BI and want to achieve  something which I have already done using sql. Following is the scenario:-   (1) I am having a table with Customer details and having columns like...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may refer to my solution in this PBI file.

     

    Hope this helps.

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

ankurdsharma  Please add a new column in the source table to flag

 

Flag = 
VAR _MaxYear = YEAR(MAX(Test262NextCheck[LastDayDate]))
VAR _Lkp = LOOKUPVALUE(Test262NextCheck[CustID],Test262NextCheck[CustID],Test262NextCheck[CustID],Test262NextCheck[LastDayDate],DATEADD(Test262NextCheck[LastDayDate],1,YEAR))
RETURN IF(ISBLANK(_Lkp) && YEAR(Test262NextCheck[LastDayDate]) <> _MaxYear,"Yes","No")

 

Then Create a measure to sum the values with flag Yes.

 

Test262 = CALCULATE(SUM(Test262NextCheck[Sales]),Test262NextCheck[Flag]="Yes")

  • ankurdsharma's avatar
    ankurdsharma
    Frequent Visitor
    7 years ago

    Thanks PattemManohar, I have another situation similar to same case as per below records

     

    ---------------------------------------------------------------

    | CustomerId | CustomerName | Sales | LastDayDate |

    ---------------------------------------------------------------

    | 1                 | ABC                   | 1000 | 2015-12-31 |

    ---------------------------------------------------------------

    | 2                 | XYZ                   | 2000 | 2015-12-31 |

    ---------------------------------------------------------------

    | 3                 | WIN                  | 1000 | 2015-12-31 |

    ---------------------------------------------------------------

    | 1                 | ABC                  | 1000 | 2016-12-31 |

    ---------------------------------------------------------------

    | 2                 | XYZ                  | 1000 | 2016-12-31 |

    ---------------------------------------------------------------

     

    I want to find out the list of 'CustomerName' who were there as on date of '2015-12-31' but not as on date of '2016-12-31'. This thing I want to achieve on the basis of Slicer value. Suppose, in slicer I have choosen date of '2016-12-31' then logic should check for results for choosen date and previous year same date('2015-12-31') and It should give resullt of CustomerName who were their earlier but not now(slicer selected date). There can be n number or records in the table with multiple dates.

     

    Please guide me.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

       

      You may refer to my solution in this PBI file.

       

      Hope this helps.