Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
RomuloBarbieri
Frequent Visitor

Filling Blank Values

‎05-17-2018 03:36 PM

Hello Guys.

 

Im trying to create some dashboard with SAP information (MBEWH and MARDH tables).

Those tables have the Stock Position for all materials in each month-end.

 

The problem with that is if the material stock position has not changed from one month to another, the information is not populated on the tables. There is a small sample of the table below (Sample 1).

 

I need to Populate the missing Months with the last stock position.

Steps that i did so far:

 

1. Created a Calendar Table: Z1_Month_Year = SUMMARIZE(ADDCOLUMNS(CALENDAR(Date("2000","01","01"), Today()), "Month", Month([Date]),"Year", YEAR([Date]), "Z_Year_Month", Year([Date])&"_"&MONTH([Date])), [Z_Year_Month], [Year],[Month])

 

2. Summarized Material Table: Z1_Material_Data = SUMMARIZE(SAP_MARDH,SAP_MARDH[MANDT_Client], SAP_MARDH[WERKS_Plant], SAP_MARDH[MATNR_Material])

 

3. CrossJoin Both Tables: Z1_Cross-Date_Mat = CROSSJOIN(Z1_Material_Data, Z1_Month_Year)

 

Until now i couldnt find a Function to Populate Missing values. The closest function i have (below - Function 1), is not considering that in those tables i have several Materials, Divisions, and Clients. 

 

Thanks!

 

Sample 1:

 

ClientMaterial          PlantYearMonthStock Position
1011420108DO50 20157               8.806,00
1011420108DO50 20158               8.807,00
1011420108DO50 201510                  938,00
1011420108DO50 201511                  941,00
1011420108DO50 201512                  945,00
1011420108DO50 201612                  943,00
1011420108DO50 20174                  943,00
1011420108DO50 20175                  746,00
1011420108DO50 20176                            -  
1011420108DO50 201712                            -  

 

Function1:

Z_StockPosition =
   VAR LastNonBlankDate =
          CALCULATE (
               LASTNONBLANK ( 'Z1_Cross-Date_Mat'[Z_EndOfMOnth], 1 ),
                 FILTER (
                    ALL ( 'Z1_Cross-Date_Mat'),
                    'Z1_Cross-Date_Mat'[Z_EndOfMOnth] <= EARLIER ( 'Z1_Cross-Date_Mat'[Z_EndOfMOnth] )
                     && NOT ( ISBLANK ( 'Z1_Cross-Date_Mat'[Z_StockPositionHL] ) )
))
RETURN
       CALCULATE (
           MAX ( 'Z1_Cross-Date_Mat'[Z_StockPositionHL] ),
               FILTER ( ALL ( 'Z1_Cross-Date_Mat' ), 'Z1_Cross-Date_Mat'[Z_EndOfMOnth] = LastNonBlankDate )
)

This is how the full table looks like:  

 

ClientMaterial          PlantYearMonthStock Position
1011420108DO50 201260
1011420108DO50 201270
1011420108DO50 201281.766,00
1011420108DO50 201291.884,00
1011420108DO50 2012110
1011420108DO50 201212163
1011420108DO50 201370
1011420108DO50 201382.016,00
1011420108DO50 201392.173,00
1011420108DO50 2013104.997,00
1011420108DO50 2013115.591,00
1011420108DO50 2013125.998,00
1011420108DO50 201416.138,00
1011420108DO50 201426.216,00
1011420108DO50 201436.503,00
1011420108DO50 201442.259,00
1011420108DO50 201453.432,00
1011420108DO50 201468.591,00
1011420108DO50 201478.619,00
1011420108DO50 201488.656,00
1011420108DO50 201498.745,00
1011420108DO50 2014108.755,00
1011420108DO50 2014118.765,00
1011420108DO50 2014128.763,00
1011420108DO50 201518.765,00
1011420108DO50 201528.792,00
1011420108DO50 201548.797,00
1011420108DO50 201558.799,00
1011420108DO50 201568.803,00
1011420108DO50 201578.806,00
1011420108DO50 201588.807,00
1011420108DO50 201510938
1011420108DO50 201511941
1011420108DO50 201512945
1011420108DO50 201612943
1011420108DO50 20174943
1011420108DO50 20175746
1011420108DO50 201760
1011420108DO50 2017120
1011420108DO50 201840
1011420109DO50 201260
1011420109DO50 201270
1011420109DO50 2012110
1011420109DO50 2012128.325,00
1011420109DO50 201380
1011420109DO50 201390
1011420109DO50 20131066
1011420109DO50 20131166
1011420109DO50 2013120
1011420109DO50 201410
1011420109DO50 20142198
1011420109DO50 20143396
1011420109DO50 20144660
1011420109DO50 20145264
1011420109DO50 20146330
1011420109DO50 20147858
1011420109DO50 20148944
1011420109DO50 20149990
1011420109DO50 201410990
1011420109DO50 2014111.452,00
1011420109DO50 2014128.052,00
1011420109DO50 20151594
1011420109DO50 201526.666,00
1011420109DO50 201538.118,00
1011420109DO50 201546.402,00
1011420109DO50 20155858
1011420109DO50 201565.544,00
1011420109DO50 2015713.332,00
1011420109DO50 2015821.583,00
1011420109DO50 201593.147,00
1011420109DO50 2015104.021,00
1011420109DO50 2015115.671,00
1011420109DO50 2015124.886,00
1011420109DO50 201615.757,00
1011420109DO50 201620
1011420109DO50 201630
1011420109DO50 201640
1011420109DO50 201650
1011420109DO50 201660
1011420109DO50 201670
1011420109DO50 201680
1011420109DO50 201690
1011420109DO50 2016100
1011420109DO50 2016110
1011420109DO50 2016120
1011420109DO50 201710
1011420109DO50 201720
1011420109DO50 201730
1011420109DO50 201740
1011420109DO50 201750
1011420109DO50 201760
1011420109DO50 201770
1011420109DO50 201780
1011420109DO50 2017100
1011420109DO50 2017120
1011420109DO50 201810
1011420110DO50 201260
1011420110DO50 2012110
1011420110DO50 201212213
1011420110DO50 2013120
1011420110DO50 201410
1011420110DO50 201420
1011420110DO50 201430
1011420110DO50 2014466
1011420110DO50 201450
1011420110DO50 20146132
1011420110DO50 201470
1011420110DO50 20148132
1011420110DO50 201490
1011420110DO50 2014100
1011420110DO50 20141166
1011420110DO50 201412157
1011420110DO50 2015169
1011420110DO50 20152333
1011420110DO50 20153198
1011420110DO50 201541.452,00
1011420110DO50 20155462
1011420110DO50 2015666
1011420110DO50 201570
1011420110DO50 201580
1011420110DO50 2015966
1011420110DO50 201510594
1011420110DO50 201511990
1011420110DO50 2015121.518,00
1011420110DO50 201611.965,00
1011420110DO50 20162372
1011420110DO50 20163394
1011420110DO50 20164739
1011420110DO50 20165560
1011420110DO50 201661.302,00
1011420110DO50 201671.848,00
1011420110DO50 201682.203,00
1011420110DO50 2016911
1011420110DO50 201610108
1011420110DO50 201611262
1011420110DO50 201612141
1011420110DO50 2017153
1011420110DO50 2017247
1011420110DO50 20173272
1011420110DO50 201740
1011420110DO50 2017566
1011420110DO50 2017618
1011420110DO50 2017730
1011420110DO50 201780
1011420110DO50 201790
1011420110DO50 2017100
1011420110DO50 2017110
1011420110DO50 201712134
1011420110DO50 201810
1011420110DO50 201820
1011420110DO50 2018366
1011420110DO50 201840
Message 1 of 6
1,330 Views
0
1 ACCEPTED SOLUTION
RomuloBarbieri
Frequent Visitor
In response to anandav

‎05-19-2018 05:46 AM
@anandav wrote:

@RomuloBarbieri,

 

Please check the Example 2 in the below blog:

https://whatthetechisthat.wordpress.com/2018/05/18/filling-missing-value-between-dates/

 

In your case since you have end of month date, you can use that instead of creating a new date field as explained in the article. Make sure in Power BI your end of month field is of Date type.

 

If this solves your problem please mark this as the solution.

@anandav,

 

Thanks for the link!

I've made few adjustments, and information is properly filled!

 

This should be the final code for this problem:

 

Z_StockPositionHL_Adjusted =

Var DateLastPopulated = CALCULATE (LASTNONBLANK ( 'Z1_Cross-Date_Mat'[Z_EndOfMOnth], 1 ), FILTER (ALLEXCEPT ( 'Z1_Cross-Date_Mat', 'Z1_Cross-Date_Mat'[Y_MANDT_WERKS_MATNR] ), 'Z1_Cross-Date_Mat'[Z_EndOfMOnth] < EARLIER ('Z1_Cross-Date_Mat'[Z_EndOfMOnth] ) && NOT ( ISBLANK ( 'Z1_Cross-Date_Mat'[Z_StockPositionHL]))))

 

Var Amount = CALCULATE(Sum('Z1_Cross-Date_Mat'[Z_StockPositionHL]), Filter(ALLEXCEPT ( 'Z1_Cross-Date_Mat', 'Z1_Cross-Date_Mat'[Y_MANDT_WERKS_MATNR] ), DateLastPopulated='Z1_Cross-Date_Mat'[Z_EndOfMOnth]))

 

Return If(ISBLANK('Z1_Cross-Date_Mat'[Z_StockPositionHL]),Amount,'Z1_Cross-Date_Mat'[Z_StockPositionHL])

View solution in original post

Message 5 of 6
1,330 Views
0
5 REPLIES 5
mortd
New Member

‎06-25-2023 11:28 AM

hi, can you share how the above solution can work in my scenario?

i need to get the value for specific period using slicer - e.g. 31/12/2020

my data table only stores the period when the value last changed:

mortd_0-1687717604520.png

 

my dashboard should show:

mortd_1-1687717640765.png

thanks!

Message 6 of 6
454 Views
0
anandav
Skilled Sharer
Skilled Sharer

‎05-17-2018 07:38 PM

@RomuloBarbieri,

Check whether the below thread answered by Eric_Zhang could be of help

https://community.powerbi.com/t5/Desktop/Best-way-to-fix-missing-days-for-currency-conversion-API/m-...

 

Message 2 of 6
1,312 Views
0
RomuloBarbieri
Frequent Visitor
In response to anandav

‎05-18-2018 06:09 AM

Hey, thanks for the reply.  I've used this solution, but still not being calculating well, but almost there. 

 

Look image below:

 

A = Correclty Calculating the Filling Formula. 

B = It is not considering 0.00 as a non-Blank, and replacing it with upper value.

C = It is using the 8000.5 to fill where it should be 320.

 

 

Capture.JPG

 

 

 

Message 3 of 6
1,300 Views
0
anandav
Skilled Sharer
Skilled Sharer
In response to RomuloBarbieri

‎05-18-2018 05:12 PM

@RomuloBarbieri,

 

Please check the Example 2 in the below blog:

https://whatthetechisthat.wordpress.com/2018/05/18/filling-missing-value-between-dates/

 

In your case since you have end of month date, you can use that instead of creating a new date field as explained in the article. Make sure in Power BI your end of month field is of Date type.

 

If this solves your problem please mark this as the solution.

Message 4 of 6
1,291 Views
0
RomuloBarbieri
Frequent Visitor
In response to anandav

‎05-19-2018 05:46 AM
@anandav wrote:

@RomuloBarbieri,

 

Please check the Example 2 in the below blog:

https://whatthetechisthat.wordpress.com/2018/05/18/filling-missing-value-between-dates/

 

In your case since you have end of month date, you can use that instead of creating a new date field as explained in the article. Make sure in Power BI your end of month field is of Date type.

 

If this solves your problem please mark this as the solution.

@anandav,

 

Thanks for the link!

I've made few adjustments, and information is properly filled!

 

This should be the final code for this problem:

 

Z_StockPositionHL_Adjusted =

Var DateLastPopulated = CALCULATE (LASTNONBLANK ( 'Z1_Cross-Date_Mat'[Z_EndOfMOnth], 1 ), FILTER (ALLEXCEPT ( 'Z1_Cross-Date_Mat', 'Z1_Cross-Date_Mat'[Y_MANDT_WERKS_MATNR] ), 'Z1_Cross-Date_Mat'[Z_EndOfMOnth] < EARLIER ('Z1_Cross-Date_Mat'[Z_EndOfMOnth] ) && NOT ( ISBLANK ( 'Z1_Cross-Date_Mat'[Z_StockPositionHL]))))

 

Var Amount = CALCULATE(Sum('Z1_Cross-Date_Mat'[Z_StockPositionHL]), Filter(ALLEXCEPT ( 'Z1_Cross-Date_Mat', 'Z1_Cross-Date_Mat'[Y_MANDT_WERKS_MATNR] ), DateLastPopulated='Z1_Cross-Date_Mat'[Z_EndOfMOnth]))

 

Return If(ISBLANK('Z1_Cross-Date_Mat'[Z_StockPositionHL]),Amount,'Z1_Cross-Date_Mat'[Z_StockPositionHL])

Message 5 of 6
1,331 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All