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
Hello @v-kaiyue-msft 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
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.
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.
Hello @v-kaiyue-msft 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
This are the headers
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
Europe’s largest Microsoft Fabric Community Conference
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
