Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KernChrisW
New Member

Query Miss: Not functioning as intended, can't find the issue in my query....any thoughts?

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
0 REPLIES 0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors