Forum Discussion

victoryamaykin's avatar
victoryamaykin
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Measure to Filter out Values Based on Current Time

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()
    )
)
DAX
Filters
Relative Time Slicer
time
  • Anonymous's avatar
    Anonymous
    2 years ago

    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
)

     

     

    1. 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

     

     

     

4 Replies

    • victoryamaykin's avatar
      victoryamaykin
      Icon for Advocate I rankAdvocate I
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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
)

     

     

    1. 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's avatar
      victoryamaykin
      Icon for Advocate I rankAdvocate I
      2 years ago

      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!