cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CL1
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;

CL1_2-1633383971600.png

CL1_3-1633383990253.png

 

 

 

 

 

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.

CL1_4-1633384217199.png

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

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

 

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.


Go to My LinkedIn Page


View solution in original post

7 REPLIES 7
Jihwan_Kim
Super User
Super User

Hi, 

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

 

Picture2.png

 

Link to the 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.


Go to My LinkedIn Page


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

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.


Go to My LinkedIn Page


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

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.


Go to My LinkedIn Page


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

CL1_0-1634054358264.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!