DAX Formula for Normalized Days with Conditions

[CL1]

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]

Hi, 

Please check the link down below. All measures are in the sample pbix file.

Link to the pbix file 

[CL1]

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]

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.

[CL1]

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]

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.

[CL1]

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 · Accepted solution]

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