Forum Discussion

alexei7's avatar
alexei7
Icon for Continued Contributor rankContinued Contributor
8 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:   ReportDate ProductCode Store Status Concat...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 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

alexei7's avatar
alexei7
Icon for Continued Contributor rankContinued Contributor
8 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
Icon for Microsoft Employee rankMicrosoft Employee
8 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

  • alexei7's avatar
    alexei7
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    I haven't tested this as the user changed requirements a little so I ended up solving this a different way.

     

    Cheers for looking into it though.