topic Re: Caculated column with Dax in DAX Commands and Tips

Caculated column with Dax

Anonymous — Mon, 03 Apr 2023 14:20:49 GMT

For this data set
I want to achieve for each StoreCode the Daily Sale is not blank and >0 for last 14 days then in Comparison column I want Yes else No.
Please help me writing this dax

@freemanZ

Re: Caculated column with Dax

Wilson_ — Mon, 03 Apr 2023 15:36:49 GMT

Hello Anis,

I am not FreemanZ, but hopefully my solution below is acceptable too. 😉 Let me know if the below calculated column works for you or if it is not what you're looking for.

Comparison = VAR CurrentDate = Table1[Retail Date] VAR FilteredSales = FILTER ( Table1, DATEDIFF ( Table1[Retail Date], CurrentDate, DAY ) < 14 && DATEDIFF ( Table1[Retail Date], CurrentDate, DAY ) >= 0 ) VAR Last14DaysSales = SUMX ( FilteredSales, Table1[Daily Sales] ) RETURN SWITCH ( TRUE(), NOT ( ISBLANK ( Table1[Daily Sales] ) ) && Last14DaysSales > 0, "Yes", "No" )

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: Caculated column with Dax

tamerj1 — Mon, 03 Apr 2023 15:41:43 GMT

Hi Anonymous

please try

Comparison =
VAR CurrentDate = Sales[Retail Dates]
VAR CurrentStoreTable =
CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[StoreCode] ) )
VAR Last14Days =
FILTER (
CurrentStoreTable,
Sales[Retail Dates] <= CurrentDate
&& Sales[Retail Dates] > CurrentDate - 14
)
VAR Last14DaysWithValue =
FILTER ( Last14Days, Sales[Daily Sale] > 0 )
RETURN
IF ( COUNTROWS ( Last14DaysWithValue ) = COUNTROWS ( Last14Days ), "Yes", "No" )