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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jihwan,

     

    Thank you vey much I believe this solution worked, but i am attempting to make a stacked area chart with the sales on the y axis and the normalized days on the x axis but am not able to get the measure to be on the x axis. Is this a limitation of a measure? Any idea of a way around this issue?

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