cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abhishekc1
Frequent Visitor

Dynamically change state based on date slicer - help required

Hi All,

 

I have a table that looks like this:

 

LocationStart Date
loc 11-Jan-19
loc 213-Feb-19
loc 317-Feb-19
loc 41-Aug-18
loc 51-Jun-19
loc 613-May-19
loc 712-Apr-19

 

This table has a one to many relationship with my main data (DATA) table that contains an order date. This DATA table also has a many to one relationship with a date table that i created. I would need your help with the following:

 

I need to add a state to each of these locations based on the date selected using a slicer/filter. The expected output for Feb 2019, May 2019 and June 2019 could be as follows:

 

LocationFeb StateMay StateJune State
loc 1OldOldOld
loc 2NewOldOld
loc 3NewOldOld
loc 4OldOldOld
loc 5Not launchedNot launchedNew
loc 6Not launchedNewOld
loc 7Not launchedOldOld

 

Any help would truly be appreciated. 

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Project status.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Project status.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

You are a saviour! I can’t thank you enough for this Ashish. Thanks a lot for taking some time out to help me out

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I could use some more help though Ashish. One small modifications i what i am looking for:

 For example: 

 

If the slicer was set for dates between Feb 2019 to May 2019:

 

a) Locations launched in June 2019 - need to be classified as Not yet launched

b) Locations launched in March and April 2019 - need to be classified as New

c) Locations launched before March 2019 -  need to be classified as old

d) Locations launched in May 2019 - need to be classified as Launched this month

Hi,

I can only understand the answer of point a.  I cannot understand the expected answer of b, c and d.  GIven an explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

It's the way we classify locations internally. basically, each location is the location of a retail store. Any store that is less than 2 months old is classified as New internally. Could you help me figure it out in this context? I'll try to work on it by modifying the formula from your solution sheet and see if i can get this done. Anyway, thank you so much once again

Hi

Share some data and in your expected format (with a slicer for time period applied), show the expected output with an explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

I just used the logic in your file and figured out a solution for my problem. I just created a month index and modified the logic from your solution. I'm very new to all of this so Thank you so much for all the help. Really appreciate it! Also, my apologies for not sharing enough details earlier. 

You are most welcome.  Glad you could find a solution by yourself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.