DAX Help

[ankurdsharma]

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

[v-danhe-msft]

Hi @ankurdsharma ,

What is your desired result? Could you want to get the value about WIN?

Regards,

Daniel He

[ankurdsharma]

Yes @v-danhe-msft, sum of sales for WIN customer.

Regards

Ankur

[PattemManohar]

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

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 · Accepted solution]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[ankurdsharma]

Thanks you @Ashish_Mathur 

[Ashish_Mathur]

You are welcome

[ankurdsharma]

Hey @Ashish_Mathur : one quick check, can we use/store that Churn customer id/id's value (which you shown in your example) like a filter in calculation at other Measures?

Regards

Ankur

[Ashish_Mathur]

Hi,

If you can show me the result you are expecting, i will be able to help you much better.

[ankurdsharma]

Hi Ashish, 

Let me explaing it to you again. The sample which you have sent and is generating CustomerID which are churned as per attached screen. What I want is to store the results specially 'CustomerID' (these can be more than one) in separate table dynamically to use them again for another calculation. Let me know if make you to understand this.

I am talking about right side table

[Ashish_Mathur]

Hi,

I do not know how to do that.