EARLIER with a new measure instead new column

[dpombal]

I have this initial data set

I require the count of  products  for the previous Existing date.

New measure

Product Count = COUNT('Table'[ProductID])

New Column

PreviousDayValue =
var PreviousDate = CALCULATE( MAX('Table'[Date]); FILTER('Table';  'Table'[Date] < EARLIER('Table'[Date])))
return CALCULATE( [Product Count]; FILTER('Table'; 'Table'[Date] = PreviousDate) )

 My problem is that Calculated columns doesn't filter if i add slicers to  my report. Below I add an example with an slicer for product type.

PrevDay Value only works if I do not use any slicer, how to convert this in a new measure?

[v-qiuyu-msft]

Hi @dpombal,

You can create a measure below: 

Measure = var PreDate=MAXX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])),'Table'[Date])
return CALCULATE(COUNT('Table'[ProductID]),FILTER(ALLSELECTED('Table'),'Table'[Date]=PreDate)) 

Best Regards,
Qiuyun Yu 

[dpombal]

Hi @v-qiuyu-msft  your solution is close to work for me , just a little issue

If i add a period slicer with (like below) day 1 of each month is not calculated since it requires data from last day of previous month

Here it is the PBIX https://1drv.ms/u/s!Am7buNMZi-gwi298rItJUbxzPuSU

[v-qiuyu-msft · Accepted solution]

Hi @dpombal,

You can modify the measure like below: 

Measure = var PreDate=MAXX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])),'Table'[Date])
var tmp=CALCULATE(COUNT('Table'[ProductID]),FILTER(ALLSELECTED('Table'),'Table'[Date]=PreDate))
var selected=ALLSELECTED('Table'[ProductType])
return
IF(tmp<>BLANK(),tmp,CALCULATE(COUNT('Table'[ProductID]),FILTER(ALL('Table'),'Table'[Date]=PreDate && 'Table'[ProductType] in selected)) )

By the way, as your previous issue is solved based on my previous post. If you have more requirements, please create a new thread and share detail information. 

Best Regards,
Qiuyun Yu 

[dpombal]

Hi @v-qiuyu-msft just one final question, If i require more slicers like product type. Is it correct adding it on ALLSELECTED? or which is an aproximate syntax

Measure = var PreDate=MAXX(FILTER(ALL('Table');'Table'[Date]<MAX('Table'[Date]));'Table'[Date])
var tmp=CALCULATE(COUNT('Table'[ProductID]);FILTER(ALLSELECTED('Table');'Table'[Date]=PreDate))
var selected=ALLSELECTED('Table'[ProductType], 'Table'[Othercol))  
return
IF(tmp<>BLANK();tmp;CALCULATE(COUNT('Table'[ProductID]);FILTER(ALL('Table');'Table'[Date]=PreDate && 'Table'[ProductType] in selected)) )

. I don't understand correctly. what you tell me for creating new threads.

[dpombal]

Here there is a link to PBIX report

https://1drv.ms/u/s!Am7buNMZi-gwi2wTE_usJwezZMNE

[Ashish_Mathur]

Hi,

I first created a calendar table using this formula and then created a relationship.  I dragged the date from the calendar table into the visual

=CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

I then created the following calculated field formula to compute the previous day producr count

=if(OR(ISBLANK([Product Count]),ISBLANK(CALCULATE(MAX('Table'[Date]),DATESBETWEEN('Calendar'[Date],DATE(2017,1,1),MAX('Calendar'[Date])-1)))),BLANK(),CALCULATE([Product Count],DATESBETWEEN('Calendar'[Date],CALCULATE(MAX('Table'[Date]),DATESBETWEEN('Calendar'[Date],DATE(2017,1,1),MAX('Calendar'[Date])-1)),CALCULATE(MAX('Table'[Date]),DATESBETWEEN('Calendar'[Date],DATE(2017,1,1),MAX('Calendar'[Date])-1)))))