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.
Hello Everyone,
I have written DAX command as mentioned below to get the running sum based on the year month but for the month Jan I need to add constant value and then each month internal and external value till december.
But when i try to filter for the businessmodel when the value is blank its giving the value in column chart when no filter is slected which is wrong because it giving correct value in the card visual.
How can i fix this issue can anyone Please Help?
Actual_Incoming =
var yearval = SELECTEDVALUE(Date[year])
var BusinessM = SELECTEDVALUE(Orderbook[BusinessM])
var Forjan = [CurrentOrder_internal] + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "January" , Date[year] = yearval )
var forfeb = Forjan + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "February" ,Date[year] = yearval )
var ForMar = forfeb + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "March" , Date[year] = yearval )
var ForApr = ForMar + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "April" , Date[year] = yearval )
var Formay = ForApr + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "May" ,Date[year] = yearval )
var ForJun = Formay + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "June" , Date[year] = yearval )
var ForJul = ForJun + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "July" ,Date[year] = yearval )
var ForAug = ForJul + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "August" , Date[year] = yearval )
var forsep = ForAug + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "September" , Date[year] = yearval )
var foroct = forsep + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "October" , Date[year] = yearval )
var ForNov = foroct + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "November" , Date[year] = yearval )
var ForDec = ForNov + CALCULATE(([Incoming_Internal] - [Delivery_Internal]),( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) , 'Date'[MonthName] = "December" ,Date[year] = yearval )
RETURN
IF(SELECTEDVALUE('Date'[MonthName] ) = "January" , ForJAn ,
IF(SELECTEDVALUE('Date'[MonthName] ) = "February" , ForFeb,
IF(SELECTEDVALUE('Date'[MonthName] ) = "March" , ForMar,
IF(SELECTEDVALUE('Date'[MonthName] ) = "April" , ForApr,
IF(SELECTEDVALUE('Date'[MonthName] ) = "May" , ForMay,
IF(SELECTEDVALUE('Date'[MonthName] ) = "June" ,ForJun ,
IF(SELECTEDVALUE('Date'[MonthName] ) = "July" , Forjul,
IF(SELECTEDVALUE('Date'[MonthName] ) = "August" , ForAug,
IF(SELECTEDVALUE('Date'[MonthName] ) = "September" , ForSep,
IF(SELECTEDVALUE('Date'[MonthName] ) = "October" , forOct,
IF(SELECTEDVALUE('Date'[MonthName] ) = "November" , ForNov ,
IF(SELECTEDVALUE('Date'[MonthName] ) = "December" , ForDec)
)
)
)
))))))))
As you can see in the above image for the month 202407 the value for [Incoming_Internal] - [Delivery_Internal] is null so it should return the value ForJun but its giving me the overall value if the BusinessM filter was not on
Hi
Can you simplify your DAX to something along these lines
Actual_Incoming =
var yearval = SELECTEDVALUE(Date[year])
var monthnum = max( Date[month] ) -- use the month number column
var BusinessM = SELECTEDVALUE(Orderbook[BusinessM])
var Result = [CurrentOrder_internal] +
CALCULATE(
([Incoming_Internal] - [Delivery_Internal]),
( isblank(BusinessM) ||'Orderbook'[BusinessM] = businessm ) ,
'Date'[MonthName] <= monthnum ,
Date[year] = yearval
)
RETURN
Result
If nothing else, it might make debugging easier for you.
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 |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |