Previous Day Value

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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]

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

[PhilipTreacy]

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]

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 · Accepted solution]

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. 

[Anonymous]

Thanks so much! Is there a way to do this through a calculated column rather than a measure?

[Ashish_Mathur]

Hi,

Calculated column formulas do not respond to a change in filter/slicers.  So stick to a measure.

[Anonymous]

Hi Ashish, 

Will a measure allow me to do the following: 

I essentially have a table with col Start Date, EmpID, Dept, Specialty and i need to put it into a matrix for  both MAX date and previous max date if that makes sense. So we want to see breakdown of specialty and department for both the max date and the previous date (in a matrix) and then i also want to be able to subtract the value and see increase/decrease between the days.

Is that possible through a measure?

[Ashish_Mathur]

On a simple dataset, show me the exact result you are expecting.

[Anonymous]

Hi Ashish, 

Please find attached a sample. Essentially i need to mimic the matrix on the PBIX file for previous date (which in this case would be 22/02/21 as that's the next date prior to 1/3/21. And i would also need to find the difference between Max date and previous date for each row header (i.e. difference between count of emp by department for max date vs. previous date)

Data 

PBIX 

[Ashish_Mathur]

Hi,

Is this the result you want?  Down load the PBI file from here.

[Anonymous]

Hi Ashish, 

Thanks for your help so far. Unfortunately that's not quite what i need to do. 

Essentially the count of employees should be cumulative. For example, for March 1(which is the MAX date in my DueDate column) should be a cumulative count of employee ID from the MIN date all the way to MAX date (March 1). 

Then previous date would be Feb 22 (as that's the next date before March 1) and the total for Feb 22 would be a cumulative count of all employees from MIN date all the way till Feb 22. 

The difference between count of emp in March 1 vs count of emp in Feb 22 = increase/decrease in hires. 

In the matrix, i essentially need to be able to see for (feb 22 in this case) a breakdown of total emp count (cumulative) for each department. 

WHen i replace the measure with a cumulative count formula as opposed to a count, it's just giving me static numbers against each department, rather than an actual count by department. 

I'm not sure how to achieve this. I've tried a few measures but i keep getting static values rather than an accurate representation of the count against department. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Truly appreciate the help! Thank you!

[Ashish_Mathur]

You are welcome.