Using Earlier function

[PBI5851]

Hello,

 I am planning to get the count for the previous day

Date ID
9/1/2020 A1001
9/1/2020 B1001
9/1/2020 C1001
9/1/2020 D1001
9/1/2020 E1001
9/2/2020 A1001
9/2/2020 B1001
9/2/2020 C1001
9/2/2020 D1001
9/2/2020 E1001
9/2/2020 F1001
9/4/2020 B1001
9/4/2020 C1001
9/4/2020 D1001
9/5/2020 C1001
9/5/2020 D1001
9/5/2020 E1001
9/5/2020 F1001
9/5/2020 F1001

I created a measure DayCount = Calculate(CountA(MainAccount[ID]),userelationship(Date[date], MainAccount[Date])), which gives me. 

Date DayCount

9/1/2020 5

9/2/2020 6

9/4/2020 3

9/5/2020 5

The goal is to achieve the below. 

Date	DayCount	Change
9/5/2020	5	+2
9/4/2020	3	-3
9/2/2020	6	+1
9/1/2020	5	0

I thought of using ealier function to get the previous value and then do a difference of the two columns to get the Change and hide the "Ealier" column. But my earlier function is incorrect. 

DayPreviousCount = Countrows(Filter(MainAccount, earlier(MainAccount[date]) >= MainAccount[date])) 

Any help on how to achieve this value please.

[v-kelly-msft · Accepted solution]

Hi  @PBI5851 ,

You can create a measure as below:

Count difference = 
var _previouscount=CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Date]=MAXX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])),'Table'[Date])))
var _count=CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
Return
IF(_previouscount=BLANK(),0,_count-_previouscount)

And you will see:

Or you can create a calculated column as below：

_Countdifference = 
var _previousdate=MAXX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
var _previouscount=CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[Date]=_previousdate))
var _count=CALCULATE(COUNT('Table'[ID]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])))
Return
IF(_previouscount=BLANK(),0,_count-_previouscount)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@PBI5851 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[amitchandak]

@PBI5851 , Create a date table and try  like one of the following examples

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
Diff COlumn = datediff(maxx(filter('Table','Table'[Date]<earlier('Table'[Date]) && 'Table'[Numberf]= earlier('Table'[Numberf]) ),'Table'[Date]) ,'Table'[Date],Day)

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Refer

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...