topic Re: Reinsert filter after using ALL in a measure in DAX Commands and Tips

Reinsert filter after using ALL in a measure

Ackbar-Learner — Wed, 18 Jan 2023 03:28:24 GMT

Below dax code first removes the date filter from the visual and calculates data based on other dates from measures. This returns the total amount from First to Last.

Board10bP&LAllTransactionAmountwithSignBudgetActualized = VAR First = if(and(TODAY()>=[Board10aFirstDateSelected], TODAY()>= [Board10aLastDateSelected]),[Board10aLastDateSelected]+1,if(and(TODAY()<[Board10aFirstDateSelected], TODAY()< [Board10aLastDateSelected]),[Board10aFirstDateSelected],TODAY()+1)) VAR Last = CALCULATE(MAX( BudgetCalendar[Date] ),ALL( BudgetCalendar )) RETURN CALCULATE( [AmountWithSign], ALL(BudgetCalendar[Date]),DATESBETWEEN(BudgetCalendar[Date],First,Last) )

The issue is that i want this total data still to be summarized by the date hierarchy (year, quarter, month) in the visual. I tried my hand at Summarizecolumns but i have not grasped that yet.

Thanks in advance for a kind help.

Re: Reinsert filter after using ALL in a measure

Ackbar-Learner — Wed, 18 Jan 2023 09:57:35 GMT

amitchandak Hi Amit, maybe you could help on above. Thanks.

Re: Reinsert filter after using ALL in a measure

FreemanZ — Wed, 18 Jan 2023 15:03:52 GMT

hi Ackbar-Learner

you may try:

Board10bP&LAllTransactionAmountwithSignBudgetActualized =
VAR First = if(and(TODAY()>=[Board10aFirstDateSelected], TODAY()>= [Board10aLastDateSelected]),[Board10aLastDateSelected]+1,if(and(TODAY()<[Board10aFirstDateSelected], TODAY()< [Board10aLastDateSelected]),[Board10aFirstDateSelected],TODAY()+1))
VAR Last = CALCULATE(MAX( BudgetCalendar[Date] ), ALL( BudgetCalendar[Date] ))
RETURN
CALCULATE(
[AmountWithSign],
ALL(BudgetCalendar[Date]),DATESBETWEEN(BudgetCalendar[Date],First,Last)
)

Re: Reinsert filter after using ALL in a measure

Ackbar-Learner — Wed, 18 Jan 2023 15:36:00 GMT

Thanks a lot. It worked! It was infront of me and i could never see it 😀