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

[sharath123]

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.

[v-yanjiang-msft · Accepted solution]

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.

[ValtteriN]

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!