topic Re: Measure to Filter out Values Based on Current Time in DAX Commands and Tips

Measure to Filter out Values Based on Current Time

victoryamaykin — Wed, 27 Dec 2023 20:08:36 GMT

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()
    )
)

Re: Measure to Filter out Values Based on Current Time

Fowmy — Wed, 27 Dec 2023 19:59:42 GMT

victoryamaykin

So did you test this measure ?

Re: Measure to Filter out Values Based on Current Time

Anonymous — Thu, 28 Dec 2023 09:45:48 GMT

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 )

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

Re: Measure to Filter out Values Based on Current Time

victoryamaykin — Thu, 28 Dec 2023 18:08:24 GMT

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!

Re: Measure to Filter out Values Based on Current Time

victoryamaykin — Thu, 28 Dec 2023 18:11:53 GMT

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