Get count of Day-1, Day-7 and Day on an hourly basis and making it dynamic

[jammie_]

Hey!. I need help with creating a measure. 

I have a dataset with different categories. I am trying to get the count of the category by the day, the day before and 7 days after. I also want to trend this data against Hour basis and also acccording to the slicer date selected but I have been unable to. 

Example if 2023/03/05 is selected, the selected date count should show, day-7 count should show and day-1 count should show and it should be spread in an Hourly rate...

Sample Data:

tbl_dt	Category	source_type	created_at	Hourly
5/26/2023	BOT	WhatsApp	5/26/2023 15:41	15
5/27/2023	BOT	WhatsApp	5/26/2023 2:41	2
5/28/2023	BOT	WhatsApp	5/26/2023 1:41	1
5/29/2023	BOT	WhatsApp	5/26/2023 19:41	19
5/30/2023	BOT	WhatsApp	5/26/2023 7:41	7
5/31/2023	BOT	WhatsApp	5/26/2023 10:41	10
6/1/2023	BOT	WhatsApp	5/26/2023 15:41	15
6/2/2023	BOT	WhatsApp	5/26/2023 15:41	15
6/3/2023	BOT	WhatsApp	5/26/2023 15:41	15
6/4/2023	BOT	WhatsApp	5/26/2023 15:41	15
6/5/2023	BOT	WhatsApp	5/26/2023 21:41	21
6/6/2023	BOT	WhatsApp	5/26/2023 22:41	22
6/7/2023	BOT	WhatsApp	5/26/2023 23:41	23
6/8/2023	BOT	WhatsApp	5/26/2023 12:41	12
6/9/2023	BOT	WhatsApp	5/26/2023 13:41	13

Expectation:

Hour	Day 1	Day -1	Day -7
1	1
2	1
7	1
10	1
12	1
13	1
15	5

I expect this to be dynamic. 

I have tried this for day-7: 
res =

VAR _7 = SELECTEDVALUE(DateTime[Date])

var res = _7 -7

VAR _Temp =

    CALCULATE(

        Count(sample_data[Category]),

        FILTER(ALL(sample_data), sample_data[source_type] = "Whatsapp"), 

        FILTER(DateTime, DateTime[Date] = res))

return _Temp

But this returns blank. Every time, selected value () returns a datetime and not a date. 

How can I achieve this?

[Manoj_Nair]

@jammie_  Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up. 

Day -1_Count = 
VAR SelectedDate = VALUE(MAX(tbl_dt[tbl_dt]))
RETURN
COUNTROWS(
    FILTER(
        tbl_dt,
        VALUE(tbl_dt[tbl_dt]) = SelectedDate - 1
    )
)

Day -7_Count = 
VAR SelectedDate = VALUE(MAX(tbl_dt[tbl_dt]))
RETURN
COUNTROWS(
    FILTER(
        tbl_dt,
        VALUE(tbl_dt[tbl_dt]) = SelectedDate - 7
    )
)

[jammie_ · Accepted solution]

Hello @Manoj_Nair Thanks a lot. 

It didnt fix the issue. 

Here's what I used eventually:

d-1 =

var new =

CALCULATE(

    Count(sample_data[Category]),

    DATEADD(DateTime[Date_Alone], -1,DAY),

    sample_data[source_type] = "WhatsApp",

    NOT(sample_data[Category] in {"BOT"})

    )

var res = IF(new = BLANK(), 0, new)

return res

This way, any date selected(sliced/filtered) would return the day before the selected date

[jammie_]

Thanks @foodd .. The main question is why does selectedvalue measure return datetime and not date value as doing this returns blank. And if there are other means to achieve the above I am open. 

There are no sample as to how I want it. Its what I want to get.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.