topic Re: Solving calcuated Column problem using dax in DAX Commands and Tips

Solving calcuated Column problem using dax

Anonymous — Fri, 31 Mar 2023 05:11:01 GMT

I am facing an issue with calculated column.

This is sample data and here I want to achieve if isStoreClosed is Closed Status then I want to divide the Daily_Split Value by 6 and add this average value to the next 6 Retail Dates Daily_Split Value.

I have created the Daily_Split2 Column and written the folloing dax to achieve the result

Daily_split 2 =

VAR Closed =

CALCULATE (

SUM ( 'Daily Table'[Daily_Split] ) / 6,

FILTER (

'Daily Table',

'Daily Table'[IsStoreClosed] = "Closed"

&& 'Daily Table'[Store_Code] = EARLIER ( 'Daily Table'[Store_Code] )

&& 'Daily Table'[Retail Date] = EARLIER ( 'Daily Table'[Retail Date])

&& 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration])

&& 'Daily Table'[Category]= EARLIER('Daily Table'[Category])))

return

IF (

'Daily Table'[IsStoreClosed] = "Open",

CALCULATE (

SUM('Daily Table'[Daily_Split])+ Closed,

FILTER (

'Daily Table',

'Daily Table'[Store_Code] = EARLIER('Daily Table'[Store_Code])

&& 'Daily Table'[Retail Date] >= EARLIER('Daily Table'[Retail Date])

&& 'Daily Table'[Retail Date] <= EARLIER('Daily Table'[Retail Date]) + 6

&& 'Daily Table'[Iteration] = EARLIER('Daily Table'[Iteration])

&& 'Daily Table'[Category]=EARLIER('Daily Table'[Category])

)

))

But this Code not giving me the desired result. I am not able to add the average value to the next 6 days.
How do I fix this any help please.

Re: Solving calcuated Column problem using dax

FreemanZ — Fri, 31 Mar 2023 05:22:25 GMT

hi Anonymous

It would be good if you could simplify your case and provide the value of your expected column directly.

Re: Solving calcuated Column problem using dax

Anonymous — Fri, 31 Mar 2023 05:33:43 GMT

Hi FreemanZ , So in this screenshot if you see I have created a Daily_split 2 where I have divided the Value of Daily_split by 6 (Avg_Value) where isStoreClosed is in "Closed" Status which is Sunday
And now I wanted to add this value (Avg_Value) with the Daily_split value to the next 6 retail dates where the store is Open status and leave the row blank for "Closed" dates.

Re: Solving calcuated Column problem using dax

Anonymous — Fri, 31 Mar 2023 05:43:18 GMT

FreemanZ

In this SS the Daily_Split_Final is my Expected column with values

Re: Solving calcuated Column problem using dax

FreemanZ — Fri, 31 Mar 2023 06:42:43 GMT

hi Anonymous

try to add a column like:

Column2 = VAR _lastclosedsplit = MAXX( TOPN( 1, FILTER( TableName, TableName[Date]<EARLIER(TableName[Date]) &&TableName[IsClosed]="Closed" ), TableName[Date] ), TableName[DailySplit] ) RETURN IF( [IsClosed] = "Closed", BLANK(), [DailySplit] + DIVIDE(_lastclosedsplit, 6 ) )

Column = VAR _lastcloseddate = MAXX( FILTER( TableName, TableName[Date]<EARLIER(TableName[Date]) &&TableName[IsClosed]="Closed" ), TableName[Date] ) VAR _lastclosedsplit = MAXX( FILTER( TableName, TableName[Date]=_lastcloseddate ), TableName[DailySplit] ) RETURN IF( [IsClosed] = "Closed", BLANK(), [DailySplit] + DIVIDE(_lastclosedsplit, 6 ) )

it worked like:

Re: Solving calcuated Column problem using dax

Anonymous — Fri, 31 Mar 2023 09:31:00 GMT

Thanks a ton FreemanZ

This really worked 😍
Can you teach me please!

Re: Solving calcuated Column problem using dax

Anonymous — Sat, 01 Apr 2023 18:28:29 GMT

How to solve this if the DailySplit is not the max number for closed dates FreemanZ ??

Re: Solving calcuated Column problem using dax

FreemanZ — Sun, 02 Apr 2023 01:28:47 GMT

hi Anonymous

it shall still work, as MAXX here is actually calculating the max on a single value itself, filtered by the first argument of MAXX. Just give it a try.