Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Formula for Normalized Days with Conditions

Hi there, I am looking to create a DAX formula that normalizes the open days from a renovation date for each store. Each store has a different renovation date and has different days that they were n...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Check the attached file down below.

     

    Normalized days CC =
    VAR afterrenovation =
    FILTER (
    Data,
    Data[Store] = EARLIER ( Data[Store] )
    && Data[Sales] > 0
    && Data[Date] > Data[Renovation Date]
    )
    VAR beforerenovation =
    FILTER (
    Data,
    Data[Store] = EARLIER ( Data[Store] )
    && Data[Sales] > 0
    && Data[Date] < Data[Renovation Date]
    )
    VAR result =
    SWITCH (
    TRUE (),
    Data[Date] = Data[Renovation Date], 0,
    Data[Date] > Data[Renovation Date], RANKX ( afterrenovation, Data[Date],, ASC ),
    Data[Date] < Data[Renovation Date], RANKX ( beforerenovation, Data[Date],, DESC ) * -1
    )
    RETURN
    IF (
    Data[Date] = Data[Renovation Date],
    0,
    IF ( Data[Sales] = 0, BLANK (), result )
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi, thank you for your feedback.

If you want to show normalized days on an X-axis, then instead of creating a measure, my suggestion is to create a calculated column in a table. Then the column can be used for the X-axis.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I have attempted to create a calculated column but even when using the example solution you provided and copying it into a calcualted column it is only giving the answer "1" for the normalized days. I beleive that it is an issue somewhere in the RANKX function but cant find which part is causing the error. Any idea what would be wrong?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but you cannot write the same formula.

    A calculated column and a calculated measure are different.

    I think, instead of just screen capturing your data model, sharing your sample pbix file will help a lot of people to look into your problem to provide a good solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately it is not allowing me to attach a sample file. It is a pretty basic sample so I have just inserted a screen shot below of what I have done. I have some DAX that gives the normalized days (just altered the measure provided before) and it is working but when there is a 0 sales day it doesnt ignore that day fully for the normalized day, ie for store A there is three 0 sales days but the normalized days jump from 4 to 8. I would like it to fully ignore the 0 sales days so the next normalized day would be 5 in that example. Any help to fix this issue would be appreciated!

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Check the attached file down below.

         

        Normalized days CC =
        VAR afterrenovation =
        FILTER (
        Data,
        Data[Store] = EARLIER ( Data[Store] )
        && Data[Sales] > 0
        && Data[Date] > Data[Renovation Date]
        )
        VAR beforerenovation =
        FILTER (
        Data,
        Data[Store] = EARLIER ( Data[Store] )
        && Data[Sales] > 0
        && Data[Date] < Data[Renovation Date]
        )
        VAR result =
        SWITCH (
        TRUE (),
        Data[Date] = Data[Renovation Date], 0,
        Data[Date] > Data[Renovation Date], RANKX ( afterrenovation, Data[Date],, ASC ),
        Data[Date] < Data[Renovation Date], RANKX ( beforerenovation, Data[Date],, DESC ) * -1
        )
        RETURN
        IF (
        Data[Date] = Data[Renovation Date],
        0,
        IF ( Data[Sales] = 0, BLANK (), result )
        )