topic Re: Dax formula to calculate except for the particular date range , I need all remaining sales in DAX Commands and Tips

Dax formula to calculate except for the particular date range , I need all remaining sales

sharath123 — Sat, 22 Jan 2022 11:18:34 GMT

last 2 weeks uncleared sales =

VAR Date_Range= DATESINPERIOD(Date_Table[Date], LASTDATE(Date_Table[Date]) -WEEKDAY(LASTDATE(Date_Table[Date]),2),-14,DAY)

Return

CALCULATE(
CONCATENATEX(
VALUES(TABLE[Sales item]),Table[sales item],UNICHAR(10)),
Date_Ranage
)

it is giving the output for last 2 weeks sales. but I need sales for all the dates except the given date_range.

ex: consider in the sales table I have sales data from 1st Jan to 22nd Jan

my VAR Date_Range expression gives 3rd Jan to 16th January.
so I need output as
**(1st jan to 2jan sales)+( 17th Jan to 22nd Jan)**
i.e remaining days sales except for the given date_range.

please help me with this. thanks in advance.

Re: Dax formula to calculate except for the particular date range , I need all remaining sales

ValtteriN — Sat, 22 Jan 2022 13:19:29 GMT

Hi,

Since you have a measure now for the undesired range we can just calculate ALL sales and subtract your measure from that:

[All sales] = CALCULATE(SUM(Table[Sales]),ALL(Table))
[All but range]=[All sales]- [last 2 weeks sales]

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Dax formula to calculate except for the particular date range , I need all remaining sales

v-yanjiang-msft — Wed, 26 Jan 2022 08:04:00 GMT

Hi sharath123 ,

According to your description, I create a sample.

I modify your formula like this:

last 2 weeks uncleared sales = VAR Date_Range = DATESINPERIOD ( Date_Table[Date], LASTDATE ( Date_Table[Date] ) - WEEKDAY ( LASTDATE ( Date_Table[Date] ), 2 ), -14, DAY ) RETURN CALCULATE ( CONCATENATEX ( VALUES ( 'Table'[Sales item] ), 'Table'[sales item], UNICHAR ( 10 ) ), FILTER ( ALL ( 'Table' ), NOT ( 'Table'[Date] IN ( Date_Range ) ) ) )

Then get the expected result. The red line marked is the 1st jan to 2nd jan sales, the green line marked is the 17th jan to 22th jan sales.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.