Reply
spartanboy
Helper II
Helper II
Partially syndicated - Outbound

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

Syndicated - Outbound

@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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

Syndicated - Outbound

@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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)