Forum Discussion

hr_tetra's avatar
hr_tetra
Icon for Helper I rankHelper I
5 years ago
Solved

Compare current progress today with yesterday at the same time

Hi all,

 

I have a directquery report of the production progress during the day. I would like to add a measure/calc column which figures out what the progress was at the same time as NOW() for yesterday. Does anybody have a solution for this problem?

 

Status today current time = 5000 units (have this)

Status yesterday at current time = 4000 units (want to calculate this)

 

The point is to see if we are in front of or behind compared to the day before were we produced x units. Another variant would be to find the average units produced for the last week at this time of day, compared to today.

 

Best regards,

 

Ole

Need Help
  • TheoC's avatar
    TheoC
    5 years ago

    hr_tetra give the following a go as a Calculated Column to return records from "yesterday's" balance.  It will allocate a "1" if they do. From here, you can some all amounts in your 'values' column that have a 1 allocated for the prior day.

     

    CurTimevPrevDay = 

    VAR _PriorDayValue = IF ( 'Table'[Date and Hour Column] = TODAY () - 1 , 1 , 0 )
    VAR _LessThanCuTime = IF ( 'Table'[Date and Hour Column] <= NOW () -1 , 1 , 0 )
    VAR _ConverToHour = HOUR ( 'Table'[Date and Hour Column] )
    VAR _PriorDay = IF ( AND ( _PriorDayValue = 1 , _LessThanCuTime = 1 ) , 1 , 0 )

    RETURN

    _PriorDay

    Let me know if you need a formula for the sum of the values, however, it should be quite simple such as using a measure to sum the new column:

    SumPrevDay = CALCULATE ( SUM ( Table1[CurTimevPrevDay] ) , FILTER ( 'Table' , Table[CurTimevPrevDay] = 1 ) )


    Hope this helps! 🙂

13 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi hr_tetra 

     

    You can use PREVIOUSDAY function to achieve this.  

     

    Here is a link: https://docs.microsoft.com/en-us/dax/previousday-function-dax

     

    Once you have created a measure for Previous Day, you can then create a second measure being Current Day v Previous Day to calculate and present the variance between both (e.g. Variance Measure = [Current Day] - [Previous Day] ).

     

     

    Hope it helps 🙂

     

    Theo

    • hr_tetra's avatar
      hr_tetra
      Icon for Helper I rankHelper I
      5 years ago

      Thanks for the reply, Theo!

       

      Does PREVIOUSDAY() work for times as well? When I try this with the EventTime column I get an error, saying there are several dates. Using MAX(EventTime) within PREVIOUSDATE() does not do the trick.

       

      What I want is not compare today with yesterday, , I want to compare the progress so far today with the progress at the same time yesterday, not the total for yesterday. 

       

      Best regards,

       

      Ole

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        5 years ago

        hr_tetra give the following a go as a Calculated Column to return records from "yesterday's" balance.  It will allocate a "1" if they do. From here, you can some all amounts in your 'values' column that have a 1 allocated for the prior day.

         

        CurTimevPrevDay = 

        VAR _PriorDayValue = IF ( 'Table'[Date and Hour Column] = TODAY () - 1 , 1 , 0 )
        VAR _LessThanCuTime = IF ( 'Table'[Date and Hour Column] <= NOW () -1 , 1 , 0 )
        VAR _ConverToHour = HOUR ( 'Table'[Date and Hour Column] )
        VAR _PriorDay = IF ( AND ( _PriorDayValue = 1 , _LessThanCuTime = 1 ) , 1 , 0 )

        RETURN

        _PriorDay

        Let me know if you need a formula for the sum of the values, however, it should be quite simple such as using a measure to sum the new column:

        SumPrevDay = CALCULATE ( SUM ( Table1[CurTimevPrevDay] ) , FILTER ( 'Table' , Table[CurTimevPrevDay] = 1 ) )


        Hope this helps! 🙂