The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
Solved! Go to Solution.
@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
@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
User | Count |
---|---|
78 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
52 | |
50 | |
46 |