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
PietroFarias
Resolver II
Resolver II

Return LAST DATE from table

Hi! I'm trying calculate the percentual change of a value by the value of the last date. In my table, there is a columns with the update date. I used LASTDATE, LASTNONBLANK and other, but i return only same day of update date and not the last update date. 

 

I hope you understand the example below. I've a Sales Table where and is updated whenever there is an update in the database. For example:

And i've other Table with the Periods:

 

I want to see the Percentage change from one date to another. How can i do this with DAX?

1 ACCEPTED SOLUTION

Thanks Charlie!
How i'm a noob with DAX, i went to study about this functions. So, i came to an adaptation to know the amount of sales made.

 

First, i retrieved the last date in a column:

 

LstDate = MAXX(
               FILTER(
                      Sales;
                      Sales[Date] < EARLIER(Sales[Date])
               );Sales[Date]
          )

 

Second, i retrieved the number of rows in a filter:

LstSale = COUNTROWS(
FILTER(
Sales;
Sales[Name] = EARLIER(Sales[Name]) &&
Sales[Date]=EARLIER(sales[LstDate])
)
)

 

And finally, i wrote a measure:

 

upSales = 
VAR vlSales = SUM(Sales[LstSale])
RETURN
IF(
vlSales=BLANK();
BLANK();
DIVIDE(
[ActSales]; //Existing measure
vlSales)-1
)

 

 if i want to know the change percentage from last sale, a create a column with the last sale, similar to last example.

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@PietroFarias,

 

Create two calculated to achieve your requirement.

Rnak = RANKX(FILTER(Sale,Sale[Name]=EARLIER(Sale[Name])),Sale[Date],,ASC)
PercentageChange = (Sale[Sales]-LOOKUPVALUE(Sale[Sales],Sale[Name],Sale[Name],Sale[Rnak],Sale[Rnak]-1))/LOOKUPVALUE(Sale[Sales],Sale[Name],Sale[Name],Sale[Rnak],Sale[Rnak]-1)

Capture.PNG

 

Regards,

Charlie Liao

Thanks Charlie!
How i'm a noob with DAX, i went to study about this functions. So, i came to an adaptation to know the amount of sales made.

 

First, i retrieved the last date in a column:

 

LstDate = MAXX(
               FILTER(
                      Sales;
                      Sales[Date] < EARLIER(Sales[Date])
               );Sales[Date]
          )

 

Second, i retrieved the number of rows in a filter:

LstSale = COUNTROWS(
FILTER(
Sales;
Sales[Name] = EARLIER(Sales[Name]) &&
Sales[Date]=EARLIER(sales[LstDate])
)
)

 

And finally, i wrote a measure:

 

upSales = 
VAR vlSales = SUM(Sales[LstSale])
RETURN
IF(
vlSales=BLANK();
BLANK();
DIVIDE(
[ActSales]; //Existing measure
vlSales)-1
)

 

 if i want to know the change percentage from last sale, a create a column with the last sale, similar to last example.

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.