Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sullynivek
Frequent Visitor

calendar list for a specified date range

Hello All,

 

I want to have a list of dates as my calendar so that the user would be able to click each of these ranges and the outcome would be for my bar chart to dynamically change based on the selection:

 

The date ranges i want are:

< 14 days - 14 days ago to present day

15 - 29 days - 29 days ago to 15 days ago

30 + days - Anything that falls into 30 days ago or older

 

I have a calendar table available, the table that it will be filtering on has a due_date column also - these are linked via a relationship of Calendar[date] -> wkaw[due_date]

 

Any help would be appreciated.Thanks

 

Kevin

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @sullynivek,


You can refer to below steps to achieve your requirement, if it suitable for your requirement.

 

Steps:

1. Write dax formula to create calculated column to store range tag.

 

Range category = 
VAR diff =
    DATEDIFF ( [Date], TODAY (), DAY )
RETURN
    IF (
        diff <= 14,
        "Less than 14 days",
        IF ( diff <= 29, "15 to 29 days", "30 day or older" )
    )

 

 

3. Use above column to create a slicer to enable the filter effect.

2.gif

 

Notice: above filter effect also works on other related tables.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @sullynivek,


You can refer to below steps to achieve your requirement, if it suitable for your requirement.

 

Steps:

1. Write dax formula to create calculated column to store range tag.

 

Range category = 
VAR diff =
    DATEDIFF ( [Date], TODAY (), DAY )
RETURN
    IF (
        diff <= 14,
        "Less than 14 days",
        IF ( diff <= 29, "15 to 29 days", "30 day or older" )
    )

 

 

3. Use above column to create a slicer to enable the filter effect.

2.gif

 

Notice: above filter effect also works on other related tables.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

This didn't work for me unfortunately, the graphs did pick up on the change but it would wipe out all the bars. When looking at the table it would return 14 days or less for some spurious dates i.e. 12/07/2017?

 

Not sure why it is doing this.

 

Could you share you demo with me?

 

kevin

hi @v-shex-msft,

 

It wasn't your solution that was the problem, it was my date table; for some reason it was changing the 1st 12 date entries of the month to the US format i.e. mm/dd/yyyy and the remainder of the month the format was dd/mm/yyyy?

 

I have recitified the date table and your solution is just perfect - thanks.

 

Kevin.

 

P.S. Wonder if you could look at another problem that i have - see it at this post:

 

https://community.powerbi.com/t5/Desktop/Calculating-mean-time-between-failures/m-p/320235#M142518

 

Would be appreciated if you could help with that please - K

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors