TOPN 3 latest sale -other pattern

[Matty]

Thank you @AntrikshSharma for the explanation.

I changed the financial level to job description columns, the result is exactly the same What do you suggest?

[Matty]

Dear @BrianJ did you had time to look at it?

Thank you,

Matty

[BrianJ]

@Matty,

I stepped away from this one when I saw @AntrikshSharma step in, but I would be glad to reengage on this and see how best to get you to a final solution.

• Brian

[Matty]

Thank you @BrianJ

I did change the table references in the formula as @AntrikshSharma highlighted but the formula still throws everything up to max date in the slicer, whereas I would like to see the last 3 transactions.

Thank you,

Matty

[BrianJ]

@Matty,

Can you please post your latest PBIX work-in-progress?

Thanks!

– Brian

[Matty]

Sure,
Sales Report.pbix (963.7 KB)
Please find the attached.

Thank you,

Matty

[BrianJ]

@Matty,

FYI – been working on this one for a good chunk of the evening, leveraging off the progress that you and @AntrikshSharma have made. Have it working perfectly as a DAX query, but having some difficulty collapsing that result down into a scalar measure and returning the right value in context. I feel like I’m getting pretty close to cracking it, but I need your Excel file because I think to implement the solution I’m working on, I will need a physical index column created in Power Query on the data table.

Thanks.

– Brian

[AntrikshSharma]

@Matty You can use this

3 Latest Sales by Revenue 2 =
CALCULATE (
    [P&L Revenue],
    KEEPFILTERS (
        TOPN (
            3,
            CALCULATETABLE (
                SUMMARIZE ( Data, 'Job category'[Job category description], Data[Completed] ),
                ALLEXCEPT ( Data, 'Date'[Date] )
            ),
            [P&L Revenue]
        )
    )
)

also activate the relationship between dates and data[completed]

[BrianJ]

@AntrikshSharma,

Nice! Much cleaner and easier to follow than the Massive Mountain O’ DAX I’ve been throwing at this problem.

• Brian

[EnterpriseDNA]

Hi @Matty, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Matty]

Hi @BrianJ @AntrikshSharma

It didn’t work

The relationship is now active:

Result;

The previous pattern worked better as it didn’t return 0:

If we can’t get the Last 3 results dynamically, then I will close this topic.

Please let me know.

Thank you for your hard work!

[AntrikshSharma]

@Matty works for me:
Sales Report.pbix (938.3 KB)

[Matty]

@AntrikshSharma

Really strange I just copied your formula and still doesn;t work

Sales Report v2 .pbix (964.4 KB)

This is the weird:

Your solution provides TOP3 in overall whereas I meant to have it Last 3 sales against the max date filtered on the date slicer.

[Matty]

@AntrikshSharma @BrianJ
The below pattern gives last3 transactions

but I can’t use date filter slicer

[BrianJ]

@Matty,

Can you please provide the underlying data file? I was very close on Sunday to an alternative solution approach which may be helpful for comparison purposes, buy I need to get into PQ to create an index column for it to work.

Thanks.

• Brian

[Matty]

I think it actually now works if we are going to use this pattern:

3 Latest Sales by Revenue = SUMX(
TOPN(3, CALCULATETABLE(
SUMMARIZE(Data,Data[Completed]),ALL(‘Job category’)),
Data[Completed],DESC),
[P&L Revenue])

The only issue is the revenue it brings 0 values.

I think it works as there is a relationship between the data completed date and dates table.

@AntrikshSharma @BrianJ

2 questions:

• How can we ignore 0 values (would simple if statement work?)
• Would be possible to make inactive relation but to add somewhere in the formula the userelionship pattern?

Sales Report v2 .pbix (979.3 KB)

[BrianJ]

@Matty,

In answer to your questions:

1. I would just add the <> 0 condition to the filter pane.

2. That should work, as long as you are careful to also add that to any branched measure on which that measure depends that also uses that relationship. Tabular Editor is very useful for tracing those dependency paths.

• Brian

[Matty]

Dear @BrianJ & @AntrikshSharma

I am nearly there, I just change one reference in @AntrikshSharma formula:

It looks like it works 95%, the relationship can stay inactive and everything works, but I run into a tiny issue:

Even though the formula ignores 0 value it includes in TOP3.

The correct results should be:

31/03/2021 is not included.

The last 3 sales should be 07/04/2021, 02/04/2021 and 31/03/2021 since the revenue <> 0

How can we fix it?
Sales Report v3.pbix (979.0 KB)

[EnterpriseDNA]

Hi @Matty! I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum.

Bumping this post for the users and experts for further visibility.

[Matty]

Hi @BrianJ,

Could you please advise?

Thank you,