Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
spartanboy
Helper II
Helper II

List selected values in table/measure - Urgent!

Hello - 

I am choosing any 2 dates from a slicer, and getting cost for: prior date and current date. But, when there is no data or cost in prior date the prior cost is being filled with current cost - this is incorect, the prior cost should stay empty.

What is the correction required for below DAX?

 

Calculate_PRIOR_PERIOD_VALUE = 
VAR _selectedDates = VALUES(F_W_S_COST[TIME_FRAME])
VAR _priorperiod = MINX(FIRSTNONBLANK(_selectedDates,1),[TIME_FRAME])
VAR _prior_value = ROUND(CALCULATE([Calc_COST],F_W_SCOST[TIME_FRAME] = _priorperiod),4)

Return _prior_value 

 

 

 

Calculate_CURR_PERIOD_VALUE = 
VAR _selectedDates = VALUES(F_W_S_COST[TIME_FRAME])
VAR _currperiod = MAXX(LASTNONBLANK(_selectedDates,1),[TIME_FRAME])
VAR _curr_value = ROUND(CALCULATE([Calc_COST],F_W_SCOST[TIME_FRAME] = _priorperiod),4)

Return _curr_value 

 

 

My guess is the varaible that includes FIRSTBLANK & LASTBLANK are incorrect. How to distinguish prior date and current date?

 

P.S: cannot use the LASTDATE and FIRSTDATE function as the this is a TEXT column containing actuals dates, 2021Q1, 2021H1

 

Thanks,

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@spartanboy , not very clear, the measure should be

 

Current =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_max))

 

 

Last =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
if(_max= _min, blank(), calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_min)) )

 

 

Refer if needed

-Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@spartanboy , not very clear, the measure should be

 

Current =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_max))

 

 

Last =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
if(_max= _min, blank(), calculate( sum(Table[Value]), filter('Date', 'Date'[Date] =_min)) )

 

 

Refer if needed

-Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.