Forum Discussion

ikarouscb's avatar
ikarouscb
Frequent Visitor
2 years ago
Solved

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

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 

 

 

"DAX help"

6 Replies

  • ikarouscb's avatar
    ikarouscb
    Frequent Visitor
    2 years ago

    I can add part of the data

     

    09/05/202471650685100E630680 6305050010
    09/05/2024368635742400E14401193 1440-247-247-2471-247
    09/05/2024369635741300H180041301800-1729-2529-25292-1264.5
    09/05/2024370635741300H80041302600-2529-2529-25292-1264.5
    09/05/2024614637723600B2478 2454-382-3824-95.5
    09/05/2024615637723600B1278 3642-382-3824-95.5
    09/05/2024616637723600B478 4038-382-3824-95.5
    09/05/2024617637723600B42078 460-382-382-3824-95.5
    09/05/2024619634702200D894353118965790020
    09/05/2024620634702200D5674353116569090020
    10/05/20241592650685100E630680 1260-580-1210-12102-605
    10/05/20241593650685100E630680 1890-1210-1210-12102-605
    13/05/20241848637723600B90078 1360-1282-1282-12821-1282
    13/05/20241906634702200D216435311872-126-126-1261-126
    13/05/20241991650685100E630680 2520-1840-1840-18401-1840
    14/05/20242140650685100E630680 3150-2470-2470-24701-2470
    14/05/20242199634702200D4324353111304-558-558-5581-558
    15/05/20242326634702200D2164353111520-774-774-7741-774
    15/05/20242431637723600B90078 2260-2182-2182-21821-2182
    15/05/20242453650685100E630680 3780-3100-3100-31001-3100
    16/05/20242474635741300H120041303800-3729-3729-37291-3729
    16/05/20242550650685100E630680 4410-3730-3730-37301-3730
    16/05/20242609635742400E14401193 2880-1687-1687-16871-1687
    16/05/20242701634702200D2164353111736-990-990-9901-990
    17/05/20242779652050400A510  510-510-510-5101-510
    17/05/20242814650685100E630680 5040-4360-4990-49902-2495
    17/05/20242865634702200D4324353112168-1422-1854-18542-927
    17/05/20242880634702200D4324353112600-1854-1854-18542-927
    17/05/20242897650685100E630680 5670-4990-4990-49902-2495
    17/05/20242933637723600B90078 3160-3082-3082-30821-3082

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • ikarouscb's avatar
      ikarouscb
      Frequent Visitor
      2 years ago

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