Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I want a measure to calculate the difference between selected date and last sold date sales. And average of this difference as a grand total. But my measure is not correct. When 2023-01-17 is selected, the expected average is 5. sales of Product 3 in 2022-12-10 is also added in average. but i want to exclude this amount 12.
Sales Tabel
Id | StaffId | ProductId | Date | Sales |
1 | 1 | 1 | 12/10/2022 | 100 |
3 | 1 | 1 | 1/17/2023 | 60 |
7 | 1 | 2 | 1/17/2023 | 60 |
8 | 1 | 2 | 12/10/2022 | 10 |
9 | 1 | 3 | 12/10/2022 | 12 |
Measure
I have attached PBIX file for your reference. Appreciate your support.
Hi @haputhanthree ,
Based on the issue you raised, you can change your dax to the following:
diff =
VAR selcDate =
SELECTEDVALUE(Sales[Date])
VAR prvDate =
CALCULATE(
MAXX(
FILTER(
ALL('Sales')
,Sales[Date] < selcDate
)
,Sales[Date]
)
)
VAR PrvValue =
CALCULATE(
[Avg Sales]
,Sales[Date] = prvDate,
Sales[ProductId]<>"3"
)
VAR CrntValue =
CALCULATE(
[Avg Sales]
,Sales[Date] = selcDate,
Sales[ProductId]<>"3"
)
VAR Diff =
IF( ISBLANK(PrvValue) && NOT(ISBLANK(CrntValue))
,0
,CrntValue - PrvValue
)
RETURN
Diff
Final output:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |