Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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)
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |