Dynamic Stock balance according to PIC as at selected period

[smko]

Greetings BI community, I came across an interesting business requirement where the report has to show the stock balance of Person In Charge (PIC) as at selected period. Each PIC will have a table report (hard code to specific PIC at visual filter) showing only stock balance of his brand.

InventoryMovement

Date	Brand	PIC	Value
2023-01-01	X	A	1000
2023-02-01	X	A	-100
2023-03-01	X	B	500
2023-06-30	X	B	-800
2023-01-01	Y	A	5000

Changelog of PIC and brand

Brand	PIC	StartDate	EndDate
X	A	2023-01-01	2023-02-28
X	B	2023-03-01	2023-12-31
Y	A	2023-02-01	2023-06-30

For example:
- Selected date filter is Q1, report of PIC A should only show stock balance for Brand X as at 2023-02-28 and Brand Y as at 2023-03-31, report of PIC B show stock balance for Brand X as at 2023-03-31

- Selected date filter is Q2, report of PIC A should only show stock balance for Brand Y as at 2023-06-30, report of PIC B show stock balance for Brand X as at 2023-06-30

Desired result:

With the below code Im unable to achieve the desired result as it only calculate the value between each PIC StartDate and EndDate. The current PIC value in InventoryMovement table is following the StartDate EndDate, maybe there is a better model for this?

Stock Balance = 
VAR _MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM(InventoryMovement[Value]),
    ALLSELECTED(PIC),
    'Calendar'[Date] <= _MaxDate
)

This link contains the pbix file: test.pbix

[Anonymous]

Hi @smko ,

I have some doubt.

Why is the X in PIC Part A of 2023 Q1 900, but the X in PIC Part B of 2023 Q1 is 1400? Why doesn't PIC B ignore the PIC distinction? I may have missed some details, please help.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[smko]

Hi @Anonymous , the idea is to get stock balance up to the EndDate of the PIC. For Brand X in Q1, since EndDate of PIC A is 2023-02-28, the stock balance as at that date is only 900; while PIC B took over from 2023-03-01 onwards, at the end of Q1 the stock balance should have 1400.