Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 | 5/26/2023 15:41 | 15 | ||
5/27/2023 | BOT | 5/26/2023 2:41 | 2 | ||
5/28/2023 | BOT | 5/26/2023 1:41 | 1 | ||
5/29/2023 | BOT | 5/26/2023 19:41 | 19 | ||
5/30/2023 | BOT | 5/26/2023 7:41 | 7 | ||
5/31/2023 | BOT | 5/26/2023 10:41 | 10 | ||
6/1/2023 | BOT | 5/26/2023 15:41 | 15 | ||
6/2/2023 | BOT | 5/26/2023 15:41 | 15 | ||
6/3/2023 | BOT | 5/26/2023 15:41 | 15 | ||
6/4/2023 | BOT | 5/26/2023 15:41 | 15 | ||
6/5/2023 | BOT | 5/26/2023 21:41 | 21 | ||
6/6/2023 | BOT | 5/26/2023 22:41 | 22 | ||
6/7/2023 | BOT | 5/26/2023 23:41 | 23 | ||
6/8/2023 | BOT | 5/26/2023 12:41 | 12 | ||
6/9/2023 | BOT | 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 =
Solved! Go to Solution.
Hello @Manoj_Nair Thanks a lot.
It didnt fix the issue.
Here's what I used eventually:
This way, any date selected(sliced/filtered) would return the day before the selected date
@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
)
)
Hello @Manoj_Nair Thanks a lot.
It didnt fix the issue.
Here's what I used eventually:
This way, any date selected(sliced/filtered) would return the day before the selected date
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.
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.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |