Dax command showing different answer in chartBar

Anonymous — Tue, 14 May 2024 09:49:16 GMT

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

Re: Dax command showing different answer in chartBar

music43 — Tue, 14 May 2024 10:47:41 GMT

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.

topic Dax command showing different answer in chartBar in DAX Commands and Tips

Dax command showing different answer in chartBar

Re: Dax command showing different answer in chartBar