Incorrect measure total

Question

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.&nbsp;Sales TabelIdStaffIdProductIdDateSales11112/10/20221003111/17/2023607121/17/20236081212/10/20221091312/10/202212Measurediff&nbsp;=VAR&nbsp;selcDate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;Sales[Date]&nbsp;)VAR&nbsp;prvDate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAXX&nbsp;(&nbsp;FILTER&nbsp;(&nbsp;ALL&nbsp;(&nbsp;Sales[Date]&nbsp;),&nbsp;Sales[Date]&nbsp;&lt;&nbsp;selcDate&nbsp;),&nbsp;Sales[Date]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;PrvValue&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;[Avg&nbsp;Sales],&nbsp;Sales[Date]&nbsp;=&nbsp;prvDate&nbsp;)VAR&nbsp;CrntValue&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;[Avg&nbsp;Sales],&nbsp;Sales[Date]&nbsp;=&nbsp;selcDate&nbsp;)VAR&nbsp;Diff&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK&nbsp;(&nbsp;PrvValue&nbsp;)&nbsp;&amp;&amp;&nbsp;NOT&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;CrntValue&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CrntValue&nbsp;-&nbsp;PrvValue&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;DiffPBIX file&nbsp;I have attached PBIX file for your reference. Appreciate your support.&nbsp;

anonymous · Answer

Hi&nbsp;haputhanthree&nbsp;,
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] &lt; selcDate
        )
    ,Sales[Date]
    )
)
VAR PrvValue =
CALCULATE(
    [Avg Sales]
    ,Sales[Date] = prvDate,
    Sales[ProductId]&lt;&gt;"3"
)
VAR CrntValue =
CALCULATE(
    [Avg Sales]
    ,Sales[Date] = selcDate,
    Sales[ProductId]&lt;&gt;"3"
)
VAR Diff = 
IF( ISBLANK(PrvValue) &amp;&amp; NOT(ISBLANK(CrntValue))
    ,0
    ,CrntValue - PrvValue
)
RETURN
   Diff
&nbsp;
Final output:

&nbsp;

haputhanthree · Answer

&nbsp;Anonymous&nbsp;Thank you for your reply.&nbsp;But i am looking for something dynamic since there are multiple products in the actual scenario rather than&nbsp;Sales[ProductId]&lt;&gt;"3"&nbsp;

Forum Discussion

Incorrect measure total

2 Replies

Related content

Measure Total Incorrect

Incorrect turnover total

Measure returning incorrect value

% of total measure

Matrix period showing incorrect total when using previous year measure

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