Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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
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
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
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
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.
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)))))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |