The below power query is designed to - if functioning - only show/return certain time window sales aggregations based on the time. For example: IF its 8:15 AM, show the "8 am" time bucket, if its 10:30 am, show the 8 am and 10 am time buckets, but not the 12 noon, 2 and 4 time buckets. Etc.
What IS happenning is all the time buckets are being shown, meaning if its 1:15 pm it is showing the 8 am(correctly), the 10 am(correctly), the noon(correctly), and then on the "2 pm" colum its showin all the sales up to 1:15 pm, which it is not supposed to do, that column shouldnt "fill"/show until = or after 2pm.
Any thoughts? The day light savings could be an outside variable impacting......
------------------------------------------------------------------------------
USE ME Today's sum time buckets =
VAR hour_now = [VAR hour_now]
VAR __temptable =
ADDCOLUMNS (
CALCULATETABLE ( SUMMARIZE ( 'Time', 'Time'[Hour] ) ),
"net sales",
CALCULATE (
[SUM NET UNITS],
FILTER ( 'Calendar', 'Calendar'[istoday3] = TRUE () )
)
)
VAR __temptableUTC =
ADDCOLUMNS (
CALCULATETABLE ( SUMMARIZE ( 'Time', 'Time'[Hour] ) ),
"net sales",
CALCULATE (
[SUM NET UNITS],
FILTER ( 'Calendar', 'Calendar'[IsToday UTC] = TRUE () )
)
)
VAR __8am =
SUMX ( FILTER ( __temptable, [Hour] < 8 ), [net units] )
VAR __10am =
SUMX ( FILTER ( __temptable, [Hour] < 10 ), [net units] )
VAR __12pm =
SUMX ( FILTER ( __temptable, [Hour] < 12 ), [net units] )
VAR __2pm =
SUMX ( FILTER ( __temptable, [Hour] < 14 ), [net units] )
VAR __4pm =
SUMX ( FILTER ( __temptable, [Hour] < 16 ), [net units )
VAR __4_12am =
SUMX ( FILTER ( __temptable, [Hour] >= 16 ), [net units] )
VAR average_7 =
CALCULATE (
[Rolling 7 day Average Net Units],FILTER ( 'Calendar', 'Calendar'[istoday3] = FALSE() )
)
VAR average_MTD =
CALCULATE (
[USE ME MTD Average]
)
VAR Previous_Month_Ave =
CALCULATE (
[USE ME Previous Month AVE]
)
VAR time_buckets =
IF (
SELECTEDVALUE ( PRODUCTS[Group Name] ) <> "N/A"
&& HASONEVALUE ( 'TodaySummary'[Key_Time_Buckets] ),
SWITCH (
TRUE (),
hour_now >= 8
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 1, __8am,
hour_now >= 10
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 2, __10am,
hour_now >= 12
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 3, __12pm,
hour_now >= 14
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 4, __2pm,
hour_now >= 16
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 5, __4pm,
hour_now >= 16
&& SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 6, __4_12am,
SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 7, average_7,
SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 8, average_MTD,
SELECTEDVALUE ( 'TodaySummary'[Key_Time_Buckets] ) = 9, Previous_Month_Ave,
" "
),
BLANK ()
)
RETURN
time_buckets
