Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
73 | |
47 | |
45 | |
17 | |
17 |