cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to show next 2 days of selected date?

Hi All, 

 

I'm new to Power BI and I am having difficulties. I have some slicers as filters and two chart as below:

Untitled.png

 

The bottom chart shows data with data according to date selected in the slicers which is straight forward. E.g. The slicer selected 15-Oct-2017, the bottom chart should shows 15-Oct-2017 as well.

 

The upper chart should shows the next 2 days of the selected chart, i.e. data of 16&17-Oct-2017 should be shown. How do I achieve this in a dynamic way so that when slicer selection change, this chart changes accordingly?

 

Thanks in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: How to show next 2 days of selected date?

Hi @ImNewUser,

I test using your sample data and get expected result. Please follow the steps below.

1. Create a Calendar table. Create Calculated columns the Year, Month, Day, and the next to days using the formula below.

Year = YEAR('Calendar'[Date])
Month = MONTH('Calendar'[Date])
Day = DAY('Calendar'[Date])
next 2 days = 'Calendar'[Date]+2


2. Create three slicers including Calendar[Year], Calendar[Month], Calendar[Day] separately. 

3. Create two measures to get the filtered data and corresponding next two day.

Selected value = SELECTEDVALUE('Calendar'[Date])
next 2 days selected = SELECTEDVALUE('Calendar'[next 2 days])


4. Create a slicer including Table[Area], create two measure to get count value the next two days  and selected date as follows.

next 2 days value11 =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        AND (
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                > [Selected value],
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                <= [next 2 days selected]
        )
    )
)


filtered date =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
            = [Selected value]
    )
)


Please review the expected result as the screenshot shown.

1.PNG

You can download the .pbix file from attachment for more details.

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: How to show next 2 days of selected date?

Hi @ImNewUser,

What your data source look like? You have one or two tables? You'd better share your sample table, you can create a fake one if your data is private, so that we can help you in dedicatedly.

Thanks,
Angelia

Highlighted
Regular Visitor

Re: How to show next 2 days of selected date?

Hi Angelia,

 

Thanks for helping. This is my first time to post a thread and i'm not sure how to attach file to here... Anyway, the data is fake and is simple, I'm pasting as below:

Record NumberStarting DateAreaCount
115/10/2017 13:00:00A12
215/10/2017 14:00:00A13
315/10/2017 15:00:00A14
415/10/2017 16:00:00A15
515/10/2017 20:00:00A16
615/10/2017 07:00:00A17
716/10/2017 09:00:00A18
816/10/2017 12:00:00A19
916/10/2017 18:00:00A20
1016/10/2017 22:00:00A21
1117/10/2017 10:00:00A22
1217/10/2017 16:00:00A23
1317/10/2017 19:00:00A24
1415/10/2017 13:00:00B25
1515/10/2017 14:00:00B26
1615/10/2017 15:00:00B27
1715/10/2017 16:00:00B28
1815/10/2017 20:00:00B29
1915/10/2017 07:00:00B30
2016/10/2017 09:00:00B31
2116/10/2017 12:00:00B32
2216/10/2017 18:00:00B33
2316/10/2017 22:00:00B34
2417/10/2017 10:00:00B35
2517/10/2017 16:00:00B36
2617/10/2017 19:00:00B37
Highlighted
Microsoft
Microsoft

Re: How to show next 2 days of selected date?

Hi @ImNewUser,

I test using your sample data and get expected result. Please follow the steps below.

1. Create a Calendar table. Create Calculated columns the Year, Month, Day, and the next to days using the formula below.

Year = YEAR('Calendar'[Date])
Month = MONTH('Calendar'[Date])
Day = DAY('Calendar'[Date])
next 2 days = 'Calendar'[Date]+2


2. Create three slicers including Calendar[Year], Calendar[Month], Calendar[Day] separately. 

3. Create two measures to get the filtered data and corresponding next two day.

Selected value = SELECTEDVALUE('Calendar'[Date])
next 2 days selected = SELECTEDVALUE('Calendar'[next 2 days])


4. Create a slicer including Table[Area], create two measure to get count value the next two days  and selected date as follows.

next 2 days value11 =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        AND (
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                > [Selected value],
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                <= [next 2 days selected]
        )
    )
)


filtered date =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
            = [Selected value]
    )
)


Please review the expected result as the screenshot shown.

1.PNG

You can download the .pbix file from attachment for more details.

Best Regards,
Angelia

View solution in original post

Highlighted
Regular Visitor

Re: How to show next 2 days of selected date?

Hi @v-huizhn-msft,

 

Thanks for the help, it does solve my problem!

Highlighted
Frequent Visitor

Re: How to show next 2 days of selected date?

How did you get starting date in table.

 

I have the similar type of doubt but i did not get the result....


@v-huizhn-msft wrote:

Hi @ImNewUser,

I test using your sample data and get expected result. Please follow the steps below.

1. Create a Calendar table. Create Calculated columns the Year, Month, Day, and the next to days using the formula below.

Year = YEAR('Calendar'[Date])
Month = MONTH('Calendar'[Date])
Day = DAY('Calendar'[Date])
next 2 days = 'Calendar'[Date]+2


2. Create three slicers including Calendar[Year], Calendar[Month], Calendar[Day] separately. 

3. Create two measures to get the filtered data and corresponding next two day.

Selected value = SELECTEDVALUE('Calendar'[Date])
next 2 days selected = SELECTEDVALUE('Calendar'[next 2 days])


4. Create a slicer including Table[Area], create two measure to get count value the next two days  and selected date as follows.

next 2 days value11 =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        AND (
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                > [Selected value],
            DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
                <= [next 2 days selected]
        )
    )
)


filtered date =
CALCULATE (
    SUM ( Table1[Count] ),
    FILTER (
        Table1,
        DATE ( YEAR ( Table1[Starting Date] ), MONTH ( Table1[Starting Date] ), DAY ( Table1[Starting Date] ) )
            = [Selected value]
    )
)


Please review the expected result as the screenshot shown.

1.PNG

You can download the .pbix file from attachment for more details.

Best Regards,
Angelia


 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors