Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
79 | |
64 | |
62 | |
56 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |