How to show next 2 days of selected date?

[ImNewUser]

Hi All, 

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

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

[v-huizhn-msft]

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

[ImNewUser]

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 Number	Starting Date	Area	Count
1	15/10/2017 13:00:00	A	12
2	15/10/2017 14:00:00	A	13
3	15/10/2017 15:00:00	A	14
4	15/10/2017 16:00:00	A	15
5	15/10/2017 20:00:00	A	16
6	15/10/2017 07:00:00	A	17
7	16/10/2017 09:00:00	A	18
8	16/10/2017 12:00:00	A	19
9	16/10/2017 18:00:00	A	20
10	16/10/2017 22:00:00	A	21
11	17/10/2017 10:00:00	A	22
12	17/10/2017 16:00:00	A	23
13	17/10/2017 19:00:00	A	24
14	15/10/2017 13:00:00	B	25
15	15/10/2017 14:00:00	B	26
16	15/10/2017 15:00:00	B	27
17	15/10/2017 16:00:00	B	28
18	15/10/2017 20:00:00	B	29
19	15/10/2017 07:00:00	B	30
20	16/10/2017 09:00:00	B	31
21	16/10/2017 12:00:00	B	32
22	16/10/2017 18:00:00	B	33
23	16/10/2017 22:00:00	B	34
24	17/10/2017 10:00:00	B	35
25	17/10/2017 16:00:00	B	36
26	17/10/2017 19:00:00	B	37

[v-huizhn-msft · Accepted solution]

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.



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

Best Regards,
Angelia

[OPS]

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.



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

Best Regards,
Angelia

---

[ImNewUser]

Hi @v-huizhn-msft,

Thanks for the help, it does solve my problem!