Dear Power BI community
What do I want to achieve?
I am looking for some assistance to help create a DAX expression so I can filter across 2 different tables
1. 'Dim date table'[DaysFromToday]
2. 'Dim time table'[HourOfDay]
So I can show what?
I want to get the average number of discharges at the same point in time (Current hour) over the last 4 weeks
For example. On average there were 15 discharges on a Friday for the last 4 weeks between 12pm-1pm
How is my data structured
My data is structured so that I can get a count of discharges for every day and fo every hour of day
| SumOfDischargeCount | DaysFromToday | HourOfDay |
| 6 | -28 | 0 |
| 6 | -28 | 3 |
| 5 | -28 | 5 |
| 6 | -28 | 6 |
| 8 | -28 | 7 |
| 5 | -28 | 8 |
| 6 | -28 | 9 |
| 18 | -28 | 10 |
| 14 | -28 | 11 |
| 13 | -28 | 12 |
| 17 | -28 | 13 |
| 20 | -28 | 14 |
| 24 | -28 | 15 |
| 27 | -28 | 16 |
| 31 | -28 | 17 |
| 31 | -28 | 18 |
| 20 | -28 | 19 |
| 26 | -28 | 20 |
| 11 | -28 | 21 |
| 10 | -28 | 22 |
| 7 | -28 | 23 |
So far my DAX looks like this:
Test CROSSJOIN =
CALCULATE (
[SumOfDischargeCount],
FILTER (
CROSSJOIN ( ALL ( 'Dim date table'[DaysFromToday] ), ALL ( 'Dim time table'[HourOfDay] ) ),
'Dim date table'[DaysFromToday] = {-7} || 'Dim date table'[DaysFromToday] = {-14} || 'Dim date table'[DaysFromToday] = {-21} || 'Dim date table'[DaysFromToday] = {-28} || 'Dim time table'[HourOfDay] <= [Current hour]
)
)
What do I also need to consider?
I need to also consider making sure I get the average
Can anyone help assist me please
