Incorrect measure total

[haputhanthree]

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

diff =
VAR selcDate =
    SELECTEDVALUE ( Sales[Date] )
VAR prvDate =
    CALCULATE (
        MAXX ( FILTER ( ALL ( Sales[Date] ), Sales[Date] < selcDate ), Sales[Date] )
    )
VAR PrvValue =
    CALCULATE ( [Avg Sales], Sales[Date] = prvDate )
VAR CrntValue =
    CALCULATE ( [Avg Sales], Sales[Date] = selcDate )
VAR Diff =
    IF (
        ISBLANK ( PrvValue ) && NOT ( ISBLANK ( CrntValue ) ),
        0,
        CrntValue - PrvValue
    )
RETURN
    Diff

PBIX file 

I have attached PBIX file for your reference. Appreciate your support. 

[Anonymous]

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:

[haputhanthree]

 @Anonymous 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"