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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ZipCan
New Member

How to get Last Date of Sales

Hello

 

I would like to create a measure that will show the Global last sales date for each category regardless of the last sales date of that particular category.

That is if my last sales date is today, 15/03/2022, I would like to show this date on every category, regardless if they have sales or not or if their last sales < 15/03/2022.  I have Sales table and a calendar table.

CategoryLast Sales DateGlobal Last Sales Date
A15/03/202215/03/2022
B1/03/202215/03/2022
C1/02/202115/03/2022
D14/03/202215/03/2022

 

What's the formula to arrive at column C. Thanks in advance.

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution Sage

The earlier answers may work great. If your date table is named Dates and your sales measure is  Total Sales = SUM(Table[Sales Amt]

Then the measure is,If needed 

Last Sales Date = CALCULATE(LASTNONBLANK('Dates'[Date],[Total Sales]), REMOVEFILTERS())

View solution in original post

9 REPLIES 9
ZipCan
New Member

@BeaBF 

 

Thanks for your suggestion.  However, it didn't work.  It's returning the last sales date of a particular category.

BeaBF
Impactful Individual
Impactful Individual

@ZipCan what do you mean? The result you reported returns to me:

BeaBF_0-1647505479117.png

Explain yourself better please.

BF

 

Whitewater100
Solution Sage
Solution Sage

The earlier answers may work great. If your date table is named Dates and your sales measure is  Total Sales = SUM(Table[Sales Amt]

Then the measure is,If needed 

Last Sales Date = CALCULATE(LASTNONBLANK('Dates'[Date],[Total Sales]), REMOVEFILTERS())

Hello Whitewater,

 

This somehow works down to the last subcategories I have.  Thank you.

tamdo95
Frequent Visitor

Hello,
You can try the following measure:

M_Last_Sales_Date =
VAR _lastSalesDateGlobal = MAX(Table[Last Sales Date Global])
RETURN
CALCULATE(_lastSalesDateGlobal,ALLSELECTED(Table[Last Sales Date Global]))

I hope this will help.

Hi tamdo95,

 

Thank you for your suggestion.  However, this formula returns the last date of sales for a particular category

PiEye
Resolver II
Resolver II

Hi, try this formula:

OverallMaxDate = maxx(ALLSELECTED('Table'),'Table'[Sales Date])
This will aggregate over the whole table. You can expand this further to ignore selections, etc.
 
PiEye_0-1647334646565.png

 

 
Let me know if this works!
 
Pi Eye
 

Hi Pi Eye,

 

Thank you for your suggestion.  It worked on the main categores i.e. A, B, C, D.  However, I have sub-categories which I have not mentioned in my original post and it didn't work for them.

BeaBF
Impactful Individual
Impactful Individual

@ZipCan Hi!

Try with this measure:

 

Last Sales Date = CALCULATE(MAX(Tabella[Last Sales Date Global]), ALL(Tabella))
 
BF

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors