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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Anonymous
Not applicable

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

 

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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