Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My problem is: I have a table of transactions and the date when those transactions ocurred; I'm looking for a way to get the las transaction value that ocurred prior to the last day of the month and year selected in the slicers.
AccountID | CompanyID | Value | Date |
1 | A | 100 | 2021-10-01 |
2 | A | 200 | 2022-09-15 |
3 | A | 300 | 2022-07-04 |
4 | B | 400 | 2021-01-02 |
1 | A | 500 | 2022-03-15 |
2 | A | 600 | 2022-05-25 |
2 | A | 700 | 2022-06-30 |
3 | A | 800 | 2022-01-13 |
Example: If I select the year 2022 and the month MAY in slicers, the measure will return 500 to the Account 1; 600 to the Account; 800 to the Account 3, because those were the values of the last transactions that ocurred prior to the last day of the month selected (May, its last day is 2022-05-31).
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
It works only if I turn off the relationship between the DATA table and the DATES table, but if I do that, when I check the month slicer for a month that hasnt been closed yet (in my case october,2022) the measure no longer works.
@Gekko1 , Try these two
Last Value =
var _max1 = maxx(allselected(Data), Data[Date])
Var _max = maxx(filter( all(Data1), Data1[AccountID] = max(Data1[AccountID]) && Data1[Date]<= _max1 ),Data1[Date])
return
CALCULATE(sum(Data1[Value]), filter( (Data1), Data1[ID] = max(Data1[ID]) && Data1[Date] =_max))
Sum Last Value = sumx(VALUES(Data1[ID]) , [Last Value])
Second one is used to sum the first one
It didn't work. The issue that I'm having now is that if the last transaction of an account happened last month, it isnt shown. For example: the last transaction of a specific account happened in september 30th, but if I check the slicer box for october this year, no transactions are shown for that account.
In the variable _max1, the table data is supposed to be calendar table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |