Get latest Balance

[Sachy123]

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

1001balance = var mindate=[minimum1001date] return CALCULATE(SUM(Data[Balance]),FILTER(all(Data),Data[DateID]=mindate && Data[COA]=1001))

minimum1001date = var dateselected=SELECTEDVALUE(Data[DateID]) return Maxx(FILTER(all(Data),Data[COA]=1001 && Data[DateID]<=dateselected),Data[DateID])

[Ashish_Mathur]

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.

[Sachy123]

Hi @Ashish_Mathur 

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sachy123]

Hi @Ashish_Mathur 

Thanks!   in your file the logic is working as expected.

but when I translate it in to my data, it throws me an error : An invalid numeric representation of a date value was encountered.

The date format in the date table was as follows 2016-06-13 00:00:00.000

Also , the COA data is a direct query..

[Ashish_Mathur]

Hi,

That does not look like a Date format.  In the Query Editor, ensure that you set the format of that column to Date.

[Sachy123]

@Ashish_Mathur  The transformation is not allowed in direct query to change column type. So the COA table is a result of direct query and the date dimension , I have pulled in the entire data set.

[amitchandak · Accepted solution]

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]

[Sachy123]

Hi @amitchandak

My version of powerbi does not support  LASTNONBLANKVALUE

[Sachy123]

still trying to figure out.. but how and where can i add the relationship?

[Sachy123]

[Sachy123]

does someone know what could be the solution? .. any hint would do!

[v-yiruan-msft]

Hi @Sachy123 ，

You can create a measure as below:

Latest Balance = 
VAR ccoa =
    MAX ( 'COA'[COA] )
RETURN
    SUMX (
        VALUES ( 'COA'[COA] ),
        CALCULATE (
            MAX ( 'COA'[Balance] ),
            FILTER ( 'COA', 'COA'[COA] = ccoa && 'COA'[DateID] = MAX ( 'COA'[DateID] ) )
        )
    )

Best Regards

Rena

[Sachy123]

@v-yiruan-msft 

well,, the coa table is a direct query,,, so basically only pulls the data for that selected date,,, so i tried this measure but it didnt work 😞

[v-yiruan-msft]

Hi @Sachy123 ,

When select the DataID as 1, 2 or 3, what will display on the visual? Could you please explain the logic of display value? Thank you.

Best Regards

Rena

[Sachy123]

@v-yiruan-msft  The expected out put is as below

Latest Balance at DateID 4
COA	Balance
1001	30
1002	80
1003	60
Latest Balance at DateID     3
COA	Balance
1001	30
1002	80
1003	100
Latest Balance at DateID    2
COA	Balance
1001	30
1002	60
1003	100
Latest Balance at DateID   1
COA	Balance
1001	50
1002	60
1003	0