Group and sum, by date range

[mitja]

Hello.

Not sure what all i tryed, but nothing is working.  the problem is that i wish to group "date" +5 days and sum "count"

table

wished Result:

date	Num	Count
11.4.2022	P01057	156
21.4.2022	3055 77251 060	39
3.5.2022	P01057	1560
9.5.2022 - 10.5.2022	P01057	1248
11.5.2022	3055 772351 060	30
16.5.2022	3055 77278 060	28
20.5.2022 - 25.5.2022	P01057	690

 

[mitja]

thank you

[v-cazheng-msft · Accepted solution]

Hi @mitja,

You can try this solution.

1 Create a Calculated column

Group =
VAR prevDate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[NUm] ),
            'Table'[date] < EARLIER ( 'Table'[date] )
        )
    )
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[NUm] ),
            'Table'[date] > EARLIER ( 'Table'[date] )
        )
    )
VAR prevDiff =
    DATEDIFF ( prevDate, 'Table'[date], DAY )
VAR nextDiff =
    DATEDIFF ( 'Table'[date], nextDate, DAY )
RETURN
    IF (
        prevDiff <= 5
            && NOT ( ISBLANK ( prevDate ) ),
        prevDate & "-" & 'Table'[date],
        IF (
            nextDiff <= 5
                && NOT ( ISBLANK ( nextDate ) ),
            'Table'[date] & "-" & nextDate,
            'Table'[date] & ""
        )
    )

2 Create a Table visual like this

Also, attached the pbix file as reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[rohit_singh]

Hi @mitja ,

I'm not sure I understand your requirement correctly. Could you please explain what you mean by group "date" +5 days ? or what exactly you're trying to achieve?

Kind regards,

Rohit