Forum Discussion
Get latest Balance
I have a COA table which comes as a result of direct query in the following format.
| DateID | COA | Balance |
| 1 | 1001 | 50 |
| 1 | 1002 | 60 |
| 2 | 1003 | 100 |
| 2 | 1001 | 30 |
| 3 | 1002 | 80 |
| 4 | 1003 | 60 |
In the Report, I would like to show all the COA;s with thier latest balances.
| Latest Balance at DateID 4 | |
| COA | Balance |
| 1001 | 30 |
| 1002 | 80 |
| 1003 | 60 |
What kind of Relationship shall I setup between the COA table and Date Dimension to show the report as above?
edit***
so after a lot of tries,, i somehow got it done by adding two measures for each COA as below
Try like
LASTNONBLANKVALUE(Table[DateID], max(Table[Balance]))
Or
LASTNONBLANKVALUE(Table[DateID], sum(Table[Balance]))Date Join should be on date ID. You can try using Date[Date] in place of Table[Date ID]
15 Replies
Try like
LASTNONBLANKVALUE(Table[DateID], max(Table[Balance]))
Or
LASTNONBLANKVALUE(Table[DateID], sum(Table[Balance]))Date Join should be on date ID. You can try using Date[Date] in place of Table[Date ID]
- still trying to figure out.. but how and where can i add the relationship?
Hi,
Will you be selecting Data ID4 in the filter/slicer or do you want the DAX formula to pick up the last balance of each COA automatically? Also, do you have actual date entries in the DateID column? I'd request you to share the actual date format that you have in the DateID column.
Yes will be selecting the dateid from the filter. well the date-id is coming via date dimension which has got format dd-mm-yyyy
