Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous Day Value

Hi All, 

 

Is anyone able to share how to get previous day values. I need to take the MAX date in my table and then calculate a count of EmpID for the day before. 

 

i.e. latest value is Feb 27, i need to find count of emp id for Feb 26. But this needs to be dynamic so that when data gets added for Feb 28, previous value = Feb 27 value. 

 

Thanks all

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Assuming you:

    1. Have a Calendar Table which has the last day as Today's date and which auto reads the last date appearing in the Start date column of the Emp table; and
    2. There is a relationship from the Start date column to the Date column of the Calendar Table

    try this measure,

     = calculate(COUNTA([EmpID]),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])-1))

    Hope this helps. 

14 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    Please always supply some sample data, otherwise I'm just guessing at your table and column names.

    You'd use something like this

    Measure = CALCULATE(COUNTROWS('Table'[EmpID]), FILTER('Table', 'Table'[Dates] = MAX('DateTable'[Date])-1 ) )

    Regards

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Phil, 

       

      Thanks for your reply.

      That seems to be returning the individual count for the day whereas i need it to cumulate up until the previous day. 


      I've created a measure that does a cumulative count but i can't seem to display the cumulative count for the previous day based on MAX date in the table. 

       

      Measure i'm using is:

       

       Count=
      CALCULATE(COUNT[EmpID],
      FILTER(ALL('Emp Data'), 'Emp Data'['Start Date] <= MAX('Emp Data'[StartDate]) 
      ))

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Assuming you:

        1. Have a Calendar Table which has the last day as Today's date and which auto reads the last date appearing in the Start date column of the Emp table; and
        2. There is a relationship from the Start date column to the Date column of the Calendar Table

        try this measure,

         = calculate(COUNTA([EmpID]),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])-1))

        Hope this helps. 

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    Your initial question said you just needed the total for the previous day.

    I'm unclear what you mean when you say cumulative.  Presumably you mean you want a total count for all dates up to and including yesterday?

    What dates are in your 'Emp Data'[StartDate] column?  Again I have to presume that it contains dates up to and including today.  If it contains dates beyond today then your count will be incorrect because MAX will return the latest date which isn't necessarily today.

    If you want a count of EmpID for all dates upto and including yesterday - assuming the latest date in Emp Data[StartDate] is today:

     

     

     Count= CALCULATE(COUNT[EmpID], FILTER(ALL('Emp Data'), 'Emp Data'['Start Date] <= MAX('Emp Data'[StartDate]) -1 ))

    Regards

    Phil