Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
haputhanthree
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. 



haputhanthree_0-1705534677548.png

Sales Tabel

IdStaffIdProductIdDateSales
11112/10/2022100
3111/17/202360
7121/17/202360
81212/10/202210
91312/10/202212


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. 

2 REPLIES 2
v-yifanw-msft
Community Support
Community 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:

vyifanwmsft_0-1705560894186.png

 

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.