How to get the recent sale in DAX

[Anonymous]

My question is let's say today is 5/10/2022 

I'm trying to get average of recent sale. I have calendar table, I know which one is sold so I can filter those.

I just dont know how to get the latest sale for example today ? then bring only that

not today check yesterday ? then bring only that

not yesterday previos day ? 

I can't find any logic to write it down.

Let me know If there's any questions 

[Anonymous]

Thank you for your help but I guess "earlier" won't be right implemented because I couldn't run it on my end.

[v-yiruan-msft]

Hi @Anonymous ,

What you are trying to create is a calculated column or a measure? If it is a measure, the EARLIER function cannot be applied to the measure. EARLIER is mostly used in the context of calculated columns. In order to give you a suitable solution quickly, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Suppose I need two measures actually, 

One is the product is released prior week
two is the product is recently sold 

and i will need to find difference between those two measures in the same product code, where they are sold.

Unfortunately, I cannot share any file here.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column as below to get the week number

Week = WEEKNUM('Table'[Date],2)

2. Create two measures as below to get the recent sales and diff between current week and prior week

Recent sale =
VAR _selprod =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selprod )
    )
VAR _recentsale =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _selprod
                && 'Table'[Date] = _maxdate
        )
    )
RETURN
    _recentsale

Diff =
VAR _selweek =
    SELECTEDVALUE ( 'Table'[Week] )
VAR _selprod =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _preweek =
    CALCULATE (
        MAX ( 'Table'[Week] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _selprod
                && 'Table'[Week] < _selweek
        )
    )
VAR _curweeksales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Product] = _selprod && 'Table'[Week] = _selweek )
    )
VAR _preweeksales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _selprod
                && 'Table'[Week] = _preweek
        )
    )
RETURN
    IF ( ISBLANK ( _preweeksales ), BLANK (), _curweeksales - _preweeksales )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi, 

Sorry for late response, 

I really appreciate your effort and explanation I will look at the pbix file now. 
Even though it's not the right approach to my question, i want to understand the logic you work on here.

I think depending on the business requirements has been updated, and interpreting here online wasn't so clear I couldn't ask my need properly. 

but, it turns out way more complicated what i was expressing.

because of the level difference now i'm not working on this task, but still if you want to know the whole idea I can give all the informations yet I can't share any data due to business confidentiality.

again I respect your time and effort.

Best
 

[BILearner]

Maybe something like this? 

Last day Avg sales =

CALCULATE(AVERAGE(Sales[SalesAmount]),FILTER('Calendar', 'Calendar'[DateKey] = MAX(Sales[DateKey])))

[Anonymous]

Hey, 

Yes that's what I exactly wrote it down 

but I want to make sure that I'm getting the 'current sale' with this measure...

which columns or filtering that i should apply to get a narrow result so, I can see it clearly ?

I have columns such as saledate, IsSold etc.

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the following links to get the sales with recent date...

Find Sales on the Last Date with DAX in Power BI

Show Value for the Last Date in Microsoft Power BI

Calculating latest date of sale

DateofLastSale =
CALCULATE (
    MAX ( Table1[DateOfSale] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[EmployeeID] ),
        Table1[DateOfSale] < EARLIER ( Table1[DateOfSale] )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards