cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpombal Member
Member

EARLIER with a new measure instead new column

I have this initial data set

 

initial data.PNG

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.

 

with without slicers.PNG

 

 

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

 

powerBI_new_column.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: EARLIER with a new measure instead new column

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)) )

 

w2.PNGw2.PNG

 

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
dpombal Member
Member

Re: EARLIER with a new measure instead new column

Here there is a link to PBIX report

 

 

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

Moderator v-qiuyu-msft
Moderator

Re: EARLIER with a new measure instead new column

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)) 

 

q4.PNGq5.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: EARLIER with a new measure instead new column

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)))))

Untitled.pngUntitled1.png

dpombal Member
Member

Re: EARLIER with a new measure instead new column

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

 

Table with period column.PNG

PowerBI Report PBIX.PNG

 

Moderator v-qiuyu-msft
Moderator

Re: EARLIER with a new measure instead new column

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)) )

 

w2.PNGw2.PNG

 

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dpombal Member
Member

Re: EARLIER with a new measure instead new column

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 83 members 1,579 guests
Please welcome our newest community members: