LASTNONBLANK & Totals in Matrix

[samdthompson]

My head is hurting after trying to figure this out, pretty sure the answer is in using LASTNONBLANK but i just cant get it to work. I have a series of suppliers who own shares. Those suppliers are in groups. I want to be able to see the shareholding of the groups as at any point in time. The current state of the matrix I have is this:

Current
Group	Supplier	1/12/2020	15/12/2020	28/12/2020	1/01/2021	12/01/2021	28/01/2021	2/02/2021
1	A	500					100
	B	400						300
	C	200			250
Group1Total		1100	0	0	250	0	100	300
2	X	300	350
	Y	400					100
Group2Total		700	350	0	0	0	100	0
GrandTotal		1800	350	0	250	0	200	300

and I want to produce this:

EndState
Group	Supplier	1/12/2020	15/12/2020	28/12/2020	2020Total	1/01/2021	12/01/2021	28/01/2021	2/02/2021	2021Total
1	A	500	500	500	500	500	500	550	550	550
	B	400	400	400	400	400	400	400	300	300
	C	200	200	200	200	250	250	250	250	250
Group1Total		1100	1100	1100	1100	1150	1150	1200	1100	1100
2	X	300	350	350	350	350	350	350	350	350
	Y	400	400	400	400	400	400	100	100	100
Group2Total		700	750	750	750	750	750	450	450	450
GrandTotal		1800	1850	1850	1850	1900	1900	1650	1550	1550

My starting point is:

TotalShares = SUM(Data[Shares]), it works fine.

My attempt to get the endstate table was this:

=CALCULATE([TotalShares],LASTNONBLANK(Date[Date],SUM(Data[Shares])

It shouldnt be this hard surely?

[samdthompson]

Bump 

[v-lionel-msft]

Hi @samdthompson ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdthompson · Accepted solution]

@v-lionel-msft, thanks but its not quite right. The totals on 01/28/2021 should read:

group 1: 750 (100+400+250)

group 2: 450 (350+100)

grand total: 1200 (750+450)

I like your solution for the dates though, havent thought of that approach before.

[v-lionel-msft]

Hi @samdthompson ,

Please provide sample data in tabular form.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdthompson]

hi @v-lionel-msft, heres a screen shot and the pbix is: PBIX example 

[samdthompson]

Hello, thanks @mahoneypat but it didnt work completely. The group totals do repeat successfully but the grand total is incorrect. If both groups have a chage on the same day its fine but if there is just one group changing the grand total sums up only that.

[mahoneypat]

Please try this measure expression instead

NewMeasure =
VAR vThisDate =
    MAX ( Date[Date] )
RETURN
    CALCULATE (
        LASTNONBLANKVALUE (
            Date[Date],
            [Total Shares]
        ),
        FILTER (
            ALL ( Date[Date] ),
            Date[Date] <= vThisDate
        )
    )

Pat