topic Re: How to repeat the last date value in Matrix when null value is calculated in DAX Commands and Tips

How to repeat the last date value in Matrix when null value is calculated

ikarouscb — Mon, 13 May 2024 19:17:58 GMT

Hello Dear Comunity

i have the following issue

Desired Result calculated on Excel manually (color Orange is marking every time the value changes on the same line from previous column)

Actual result on PBI look on 16/05 (but this happens every day) to see that the missing value affects the total of the chart

Just adding a little of context, this file calculates the material needed to fully cover customer orders, and points how many need to be produced, my issue is as follows, i have created all the tables to calculate the results, and the individual part number data is 100% correct,

the issue is that when a part is not being purchased on a specific date, the matrix canot calculate the total missing amout due to the negative value is not repeated in the following date

is there a way in which the last date value for example on the part number 637723600B repeats the value of -2182 (date15/05) over the next line cell (16/05) so this can be calculated as a ongoing missing number?

please see the detail of how i calculated each of the colum data base, & the data base information

Re: How to repeat the last date value in Matrix when null value is calculated

ikarouscb — Mon, 13 May 2024 19:21:18 GMT

I can add part of the data

09/05/2024	71	650685100E	630	680		630	50	50	0	1	0
09/05/2024	368	635742400E	1440	1193		1440	-247	-247	-247	1	-247
09/05/2024	369	635741300H	1800	41	30	1800	-1729	-2529	-2529	2	-1264.5
09/05/2024	370	635741300H	800	41	30	2600	-2529	-2529	-2529	2	-1264.5
09/05/2024	614	637723600B	24	78		24	54	-382	-382	4	-95.5
09/05/2024	615	637723600B	12	78		36	42	-382	-382	4	-95.5
09/05/2024	616	637723600B	4	78		40	38	-382	-382	4	-95.5
09/05/2024	617	637723600B	420	78		460	-382	-382	-382	4	-95.5
09/05/2024	619	634702200D	89	435	311	89	657	90	0	2	0
09/05/2024	620	634702200D	567	435	311	656	90	90	0	2	0
10/05/2024	1592	650685100E	630	680		1260	-580	-1210	-1210	2	-605
10/05/2024	1593	650685100E	630	680		1890	-1210	-1210	-1210	2	-605
13/05/2024	1848	637723600B	900	78		1360	-1282	-1282	-1282	1	-1282
13/05/2024	1906	634702200D	216	435	311	872	-126	-126	-126	1	-126
13/05/2024	1991	650685100E	630	680		2520	-1840	-1840	-1840	1	-1840
14/05/2024	2140	650685100E	630	680		3150	-2470	-2470	-2470	1	-2470
14/05/2024	2199	634702200D	432	435	311	1304	-558	-558	-558	1	-558
15/05/2024	2326	634702200D	216	435	311	1520	-774	-774	-774	1	-774
15/05/2024	2431	637723600B	900	78		2260	-2182	-2182	-2182	1	-2182
15/05/2024	2453	650685100E	630	680		3780	-3100	-3100	-3100	1	-3100
16/05/2024	2474	635741300H	1200	41	30	3800	-3729	-3729	-3729	1	-3729
16/05/2024	2550	650685100E	630	680		4410	-3730	-3730	-3730	1	-3730
16/05/2024	2609	635742400E	1440	1193		2880	-1687	-1687	-1687	1	-1687
16/05/2024	2701	634702200D	216	435	311	1736	-990	-990	-990	1	-990
17/05/2024	2779	652050400A	510			510	-510	-510	-510	1	-510
17/05/2024	2814	650685100E	630	680		5040	-4360	-4990	-4990	2	-2495
17/05/2024	2865	634702200D	432	435	311	2168	-1422	-1854	-1854	2	-927
17/05/2024	2880	634702200D	432	435	311	2600	-1854	-1854	-1854	2	-927
17/05/2024	2897	650685100E	630	680		5670	-4990	-4990	-4990	2	-2495
17/05/2024	2933	637723600B	900	78		3160	-3082	-3082	-3082	1	-3082

Re: How to repeat the last date value in Matrix when null value is calculated

ikarouscb — Mon, 13 May 2024 19:22:32 GMT

This are the headers

Re: How to repeat the last date value in Matrix when null value is calculated

Anonymous — Tue, 14 May 2024 07:30:59 GMT

Hi ikarouscb ,

Your two screenshots in message1 are very blurry and I can't make out the results you are expecting.

Would you be able to provide the pbix file with the sensitive data removed to better help you with your problem.

Best Regards,

Clara Gong

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

Re: How to repeat the last date value in Matrix when null value is calculated

ikarouscb — Tue, 14 May 2024 20:08:19 GMT

Hello Anonymous thank you very much for the support

i uploaded both files the Xlxs and BPIX, this is the link, please let me know if you have an issue to get in,

https://drive.google.com/drive/folders/1TMKwujY_Fm-vc2XPhVe6n97d9SH_KpqY?usp=sharing

at the same time these are the images again with desired condition, (i changed the parts for normal objects)

and current condition

Re: How to repeat the last date value in Matrix when null value is calculated

Anonymous — Wed, 15 May 2024 06:04:37 GMT

Hi ikarouscb ,

1. Create a calculation table and create a relationship with the original table.

Table = CALENDARAUTO()

Table 2 = DISTINCT('Data Base'[PARTNO])

2. Create a measure and fill in the data that was originally empty.

Measure 3 = VAR_sum = SUM ( 'Data Base'[OnlyNeg2 cn Dup] ) VAR_date = CALCULATE ( MAX ('Data Base'[ADJ DATE]), FILTER ( ALL ( 'Data Base' ), _sum = BLANK () && 'Data Base'[PARTNO] = MAX ( 'Table 2'[PARTNO] ) && 'Data Base'[ADJ DATE] <= MAX ( 'Table'[Date] ) ) ) VAR_blank= CALCULATE ( MAX ( 'Data Base'[OnlyNeg2 cn Dup] ), FILTER ( ALL('Data Base'), 'Data Base'[ADJ DATE] = _date && 'Data Base'[PARTNO] = MAX ( 'Table 2'[PARTNO] ) ) ) RETURN IF ( _sum = BLANK (), _blank, _sum )

3. Create measure so that the correct total is displayed in the matrix.

Measure 4 = IF ( ISINSCOPE ( 'Table 2'[PARTNO] ), 'Data Base'[Measure 3], SUMX ( ALL ( 'Table 2' ), 'Data Base'[Measure 3] ) )

4. Use new fields to display in the matrix. For more details, please view the pbix attachment.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: How to repeat the last date value in Matrix when null value is calculated

ikarouscb — Thu, 16 May 2024 15:12:34 GMT

Thank you very Much Anonymous this solved the issue!! i really apreaciate! that was awsome!!!!