Measure to Filter out Values Based on Current Time

[victoryamaykin]

I'm working on an operational dashboard to show production by location.
When the executive looks at the dashboard in the early morning before store hours, I want them to see yesterday's production numbers.
After 9 am, I want them to start seeing today's production. How do I filter based on the current time? 

Here's the if statement I'm testing:

// IF the current hour is before 9 am

IF(TIME(HOUR(UTCNOW()), MINUTE(UTCNOW()), SECOND(UTCNOW())) < TIME(9,00,00),

     // THEN show yesterday's numbers

    CALCULATE(SUMX(FactTagProduction, [ValueStocked]),

        DATEADD(FactTagProduction[DateID], -1, Day)
    ),

    // ELSE show today's numbers

    CALCULATE(SUMX(FactTagProduction, [ValueStocked]),

        FactTagProduction[DateID] = TODAY()
    )
)

[v-jiewu-msft · Accepted solution]

Hi @victoryamaykin ,

If I understand correctly, the issue is that you want to filter values based on current time. Please try the following methods and check if they can solve your problem:

1.Create the simple table. 

2.Go to the Modeling tab and click on New measure.

3.Create a measure to filter based on the current time. Enter the following DAX formula.

Measure Numbers = var _date=SELECTEDVALUE(FactTagProduction[DateID])
VAR CurrentHour = HOUR(NOW())
VAR A = YEAR(_date)
VAR B = MONTH(_date)
VAR C = DAY(_date)
VAR D =
CALCULATE(
        SUM(FactTagProduction[ValueStocked]),
        FILTER(
            'FactTagProduction',
        YEAR(TODAY() - 1) = A && MONTH(TODAY() - 1) = B && DAY(TODAY() - 1) = C
    )
)
VAR E =
CALCULATE(
        SUM(FactTagProduction[ValueStocked]),
        FILTER(
            'FactTagProduction',
        YEAR(TODAY()) = A && MONTH(TODAY()) = B && DAY(TODAY()) = C
    )
)
RETURN
IF(
    CurrentHour < 9,
    D,
    E
)

4. Move the measure numbers to the columns.

5.The result is shown below.

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[victoryamaykin]

That worked very well. The only line I had to change was the CurrentHour variable to correspond to my timezone (since the Now() function appears to be UTC by default). 

Thank you for your help!

[Fowmy]

@victoryamaykin 

So did you test this measure ?

[victoryamaykin]

Thank you for your response. The posted solution worked for me. It helped to use the FILTER instead of DATEADD.