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 not operating (ie 0 sales). Is there a way to have PowerBI show the open days for 180 days before the start date and 365 days after? I have tried using the DATEDIFF function but it still includes the 0 sales days which is not what I am looking for.

The data would be set up similar to the way shown below;

 

 

 

 

 

So as an example of what im looking for from the data above. The 3 days for store A that have no sales would be ignored in the normalized open days from renovation.

I am new to DAX and am not sure if this is possible but any advice would be much appreciated!

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

     

7 Replies

    • 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
        Icon for Super User rankSuper 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.