Creating an iterative measurement

[Anonymous]

Hi everybody,

i hope someone could help me or give me a hint how to slove my problem of creating a measurement.

Situation:

I want to create a measurement, which calculated the coverage of my stock against my forecasted demand. In Access a build up a data base, which contain a "master data" table, a "stock" table and a "demand" table. I create a left outer join on the key of material to conect the table form master data to stock to demand.

Example of Querry: 

Material	Stock	Date	Demand	Date	Description of Querry output	Solution of measurement
1	0	0	0	0	(No Stock and Demand Data, only master data available)	0
1	0	31.12.18	0	0	(No demand Data only master data and stock data available)	0
2	100	31.12.18	20	31.01.19	(Normal case, scenario 1)	1
2	100	31.12.18	20	20.01.19
2	100	31.12.18	20	10.01.19
2	100	31.12.18	20	28.02.19
2	100	31.12.18	20	20.02.19
2	100	31.12.18	20	10.02.19
3	100	31.12.18	0	31.01.19	(No Demand, scenario 2)	99
3	100	31.12.18	0	20.01.19
3	100	31.12.18	0	10.01.19
4	100	31.12.18	0	31.01.19	(Sum of Demand < stock , scenario 3)	98
4	100	31.12.18	0	20.01.19
4	100	31.12.18	20	10.01.19

Problems:

The first problem is that the demand data is on a weekly base i want to sum it up to a monthly base. I try to created a calculated colum, but my solution give me only the sum of an month and not the sum of an month on an material level.

Second problem is, if I get the demand on an weekly base should the formular reduce my stock level (max of material, because of multiple rows) iterative month by month until the stock level is equal or smaller then 0. (I think a normal count with an constraints count until Stock level is =<0 should give me the right answer).

Additional problem to the second problem:

I have more scenarions (if-clauses).

First (Normal case): If my Stock is after reducing the demand <= 0, then give me the counted mounth until the stock is 0.

Second (No Demand): If my Stock will not be reduced by demand (sum of all demand over time is 0), then should the formular should give me a constant value of "99".

Third (Sum Demand < stock): If the forcasted demand over is smaller then the stock (example stock:100 pc.Demandofmonth1=50 Demandofmonth2=30, Demandofmonth3=19, Demandofmonth4=0, Demandofmonth5=0 and so on stock will be stay on the level of 1 pc), then the formular should give out the constant value of "98".

I hope someone could help me.

Wishes sacor

[v-shex-msft]

Hi @Anonymous,

I add a calculated column to calculate remain stock amount, I think you can condition if statement based on stock amount, demand amount and remain amount to return corresponding measurement flag.

Calculate column formulas:

Remain = 
[Stock]
    - CALCULATE (
        SUM ( Table1[Demand] ),
        FILTER (
            ALL ( Table1 ),
            [Material] = EARLIER ( [Material] )
                && [Demand Date] <= EARLIER ( [Demand Date] )
        )
    )

Measurement = IF([Stock]=0,0,IF([Demand]=0,99,if([Remain]>=0,98,0)))

Measurement flag comment:
No stock or no remain, flag 0;
Has stock but no demand, flag 99;
Normal stock change, flag 98

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

thank you for your fast respond and the soltion for the measurment flag it works fine after update my excel version from 32-bit to 64-bit and extend my ram to 16GB. (80 Millions rows was to much for Excel)

Is it possible to create an count colum on a monthly base? See my example table I hope u got my point and its not to confusing.

Material	Stock	Date	Demand	Date	Remain	Solution of Count (Coverage in Month)
1	0	31.12.18	5	01.01.19	-5	0
1	0	31.12.18	5	10.01.19	-10	0
1	0	31.12.18	5	19.01.19	-15	0
1	0	31.12.18	5	28.01.19	-20	0
1	0	31.12.18	5	06.02.19	-25	0
1	0	31.12.18	5	15.02.19	-30	0
1	0	31.12.18	5	24.02.19	-35	0
1	0	31.12.18	5	05.03.19	-40	0
1	0	31.12.18	5	14.03.19	-45	0
1	0	31.12.18	5	23.03.19	-50	0
1	0	31.12.18	5	01.04.19	-55	0
1	0	31.12.18	5	10.04.19	-60	0
1	0	31.12.18	5	19.04.19	-65	0
1	0	31.12.18	5	28.04.19	-70	0
1	0	31.12.18	5	07.05.19	-75	0
1	0	31.12.18	5	16.05.19	-80	0
1	0	31.12.18	5	25.05.19	-85	0
2	50	31.12.18	5	01.01.19	45	1
2	50	31.12.18	5	10.01.19	40	1
2	50	31.12.18	5	19.01.19	35	1
2	50	31.12.18	5	28.01.19	30	1
2	50	31.12.18	5	06.02.19	25	2
2	50	31.12.18	5	15.02.19	20	2
2	50	31.12.18	5	24.02.19	15	2
2	50	31.12.18	5	05.03.19	10	3
2	50	31.12.18	5	14.03.19	5	3
2	50	31.12.18	5	23.03.19	0	3
2	50	31.12.18	5	01.04.19	-5	3
2	50	31.12.18	5	10.04.19	-10	3
2	50	31.12.18	5	19.04.19	-15	3
2	50	31.12.18	5	28.04.19	-20	3
2	50	31.12.18	5	07.05.19	-25	3
2	50	31.12.18	5	16.05.19	-30	3
2	50	31.12.18	5	25.05.19	-35	3
3	100	31.12.18	5	01.01.19	95	1
3	100	31.12.18	5	10.01.19	90	1
3	100	31.12.18	5	19.01.19	85	1
3	100	31.12.18	5	28.01.19	80	1
3	100	31.12.18	5	06.02.19	75	2
3	100	31.12.18	5	15.02.19	70	2
3	100	31.12.18	5	24.02.19	65	2
3	100	31.12.18	5	05.03.19	60	3
3	100	31.12.18	5	14.03.19	55	3
3	100	31.12.18	5	23.03.19	50	3
3	100	31.12.18	5	01.04.19	45	4
3	100	31.12.18	5	10.04.19	40	4
3	100	31.12.18	5	19.04.19	35	4
3	100	31.12.18	5	28.04.19	30	4
3	100	31.12.18	5	07.05.19	25	5
3	100	31.12.18	5	16.05.19	20	5
3	100	31.12.18	5	25.05.19	15	5

In the End I want to see in Pivot table which is feed form PowerBI the following Results:

Material	Max from Stock	Max from Count (Coverage)
1	0	0
2	50	3
3	100	5

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>Is it possible to create an count colum on a monthly base?

Please try to use following formula, it will count all same source based on material column and year month:

Count of Measurement =
CALCULATE (
    COUNT ( Table[Measurement] ),
    FILTER (
        ALL ( Table ),
        Table[Material] = EARLIER ( Table[Material] )
            && Table[Measurement] = EARLIER ( Table[Measurement] )
            && FORMAT ( Table[Demand Date], "yyyymm" )
                = FORMAT ( EARLIER ( Table[Demand Date] ), "yyyymm" )
    )
)

>>In the End I want to see in Pivot table which is feed form PowerBI the following Results:

You can create a table visual with material, stock columns and count column with 'max' summary mode.

Regards,
Xiaoxin Sheng

[Anonymous]

thank you for your fast responds and helpfully support.

Wishes

Niko