Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dax command showing different answer in chartBar

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

1 Reply

  • music43's avatar
    music43
    Advocate II
    2 years ago

    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.