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,

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