Forum Discussion

alexei7's avatar
alexei7
Continued Contributor
7 years ago
Solved

DAX help

Hi guys,

 

I'm trying to create a DAX measure and hoping someone can help.

 

As a sample of some dummy date, my data is structured like this:

 

ReportDateProductCodeStoreStatusConcat Column
15/08/201810000ManchesterLive10000Live
15/08/201810000LondonLive10000Live
15/08/201810000BrightonLive10000Live
15/08/201815000LondonNot Live15000Not Live
15/08/201815000ManchesterNot Live15000Not Live
15/08/201817000LondonLive17000Live
16/09/201810000ManchesterLive10000Live
16/09/201810000LondonLive10000Live
16/09/201810000BrightonLive10000Live
16/09/201815000LondonLive15000Live
16/09/201815000ManchesterLive15000Live
16/09/201817000LondonLive17000Live
16/09/201820000LondonLive20000Live

 

What i'm trying to achieve is a metric to see in the previous month (i.e. September), how many new products there were.

A new product is defined as a product code which has its Status = "Live" and was not live in the month before (i.e. August) - either it didnt appear at all, or it had a different status.

 

My idea at resolving this was:

1) Create the concatenated column above joining the ProductCode and the Status

2) Creating a lookup column so that looks up from the concatenated column, to a filtered version of the above table for the month before (i.e. August)

3) A simple calculate formula, which would count the non-blank cells from the column created in step 2, filtered on previous month (i.e. September).

 

I have a date dimension which joins to the "ReportDate" column here. The data dimension includes two custom columns:

 

- "InlastMonth" (i.e. "Yes" for September 2018, "No" for all other dates)

- "TwoMonthsAgo" (i.e. "Yes" for August 2018, "No" for all other dates)

 

I'm stuck on step 2: "Creating a lookup column so that looks up from the concatenated column, to a filtered version of the above table for the month before ".

 

Can anyone help (either with this step, or with a better overall solution)?

 

Dummy model available here:

https://mega.nz/#!zLxxQaRY!FoJzxlXgGSxxxAecDP6iueBIps-0sIwTtYRIGxTWdMM

 

Thanks in advance

Alex

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi alexei7,

     

    That case, please add a [Rank] column first.

    Rank =
RANKX ( Products, Products[ReportDate],, ASC, DENSE )

    Then, modify the formula in original post as below:

    Column1 =
VAR previousstatus =
    LOOKUPVALUE (
        Products[Status],
        Products[ProductCode], Products[ProductCode],
        Products[Store], Products[Store],
        Products[Rank], Products[Rank] - 1
    )
RETURN
    IF ( previousstatus = BLANK () || previousstatus = "Not Live", "New", BLANK () )

    Best regards,

    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi alexei7,

     

    I removed the relationship between data table and date dimension table. Then, create below calculated column:

    Column1 =
VAR previousstatus =
    LOOKUPVALUE (
        Products[Status],
        Products[ProductCode], Products[ProductCode],
        Products[Store], Products[Store],
        Products[ReportDate].[MonthNo], Products[ReportDate].[MonthNo] - 1
    )
RETURN
    IF ( previousstatus = BLANK () || previousstatus = "Not Live", "New", BLANK () )

     

    Best regards,

    Yuliana Gu

    • alexei7's avatar
      alexei7
      Continued Contributor
      7 years ago

      Hi v-yulgu-msft,

       

      Thank you for your reply.

       

      Unfortunately this doesnt work - I do need the relationship between the data table and the date dimension table for other calculations in my model/report.

       

      The formula you've provided only takes account of the month number and not the previous month, so if i introduced some more data, i get the following incorrect result:

       

       

       

      Here the calculation is taking about only of the month number, so August 2019 is presumed to be before September 2018.

       

      Thanks

      Alex

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi alexei7,

         

        That case, please add a [Rank] column first.

        Rank =
RANKX ( Products, Products[ReportDate],, ASC, DENSE )

        Then, modify the formula in original post as below:

        Column1 =
VAR previousstatus =
    LOOKUPVALUE (
        Products[Status],
        Products[ProductCode], Products[ProductCode],
        Products[Store], Products[Store],
        Products[Rank], Products[Rank] - 1
    )
RETURN
    IF ( previousstatus = BLANK () || previousstatus = "Not Live", "New", BLANK () )

        Best regards,

        Yuliana Gu