Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
@v-yifanw-msft Thank you for your reply.
But i am looking for something dynamic since there are multiple products in the actual scenario rather than
Sales[ProductId]<>"3"
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |