cancel
Showing results for
Did you mean:
Frequent Visitor

## 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!

1 ACCEPTED SOLUTION
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

7 REPLIES 7
Super User

Hi,

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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?

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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?

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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!

Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.