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 CustomerId, CustomerName, Sales, LastDayDate(which is having value of last day of the year) etc and if a customer is Active then it will have entry against the last day of every year and if not that means customer is not Active and will no have entry for that particular year's last date. Following is the sample data to refer:-

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

|  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    | 

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

 

(2) As per above table you can see that customer name 'WIN' (customerid-3) was active in 2015 but not in 2016.

(2) I want to achieve Calculate Sales value difference for the customer which were there in the '2015-12-31' but not in '2016-12-31'.

 

Kindly suggest.

 

Regards

Ankur

Need Help

11 Replies

      • 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")