Forum Discussion

Frequent Visitor

2 years ago

Incorrect measure total

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 se...

Need Help

Anonymous

Not applicable

2 years ago

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: