cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

2 REPLIES 2
Community Support

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:

Frequent Visitor

But i am looking for something dynamic since there are multiple products in the actual scenario rather than

Sales[ProductId]<>"3"

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors