Need help with Table Visualization error in PowerBI desktop

[Anonymous]

Hi everyone,

I have made a table visualization of my data for my dashboard which shows daily data, 7 days rolling average & cumulative total.

I have two tables presenting different data sets, but left table (as you can see in the pic: "Daily Fitup Inch Dia" is showing an error where it fills up empty spaces with the inital rolling average which was "5.71" in all the empty spaces. 

But the table right next to it: Daily Weld Inch Dia (with is using the exact same syntax for the measures, but ofc different data set) is displaying without errors.

Please help me out on how I can fix this.

[Anonymous]

Syntax for Rolling Average:

7 DAYS FITUP INCH AVG =

CALCULATE(

SUM('FITUP INCH DIA'[FITUP Inch DIA]),

DATESINPERIOD(Dates[Date], LASTDATE('FITUP INCH DIA'[Date]),-7,DAY)

)

/7

 Below is some sample data from the Sheet: FITUP INCH DIA

TYPE	DRAWINGNUMBER	SPOOLNO	WELDNO/JOINTNO	FITUP Inch DIA	SCH/WT	Stream	Skid	Batch	Date
DUPLEX HEADER	W1WRS01-BR005-0250-P3400-PER	W1WRS01-BR005-0250-P3400-PER-02	J-16	10	Sch 5S	3	2	6	9/17/2020
DUPLEX HEADER	W1WRS01-BR005-0250-P3400-PER	W1WRS01-BR005-0250-P3400-PER-02	J-16	10	Sch 5S	3	3	7	9/17/2020
DUPLEX HEADER	W1WRS01-BR005-0250-P3400-PER	W1WRS01-BR005-0250-P3400-PER-02	J-16	10	Sch 5S	3	4	8	9/17/2020
DUPLEX HEADER	W1WRS01-BR006-0500-P3400-PER	W1WRS01-BR006-0500-P3400-PER-01	J-06	20	Sch 5S	3	1	5	9/9/2020
DUPLEX HEADER	W1WRS01-BR006-0500-P3400-PER	W1WRS01-BR006-0500-P3400-PER-01	J-06	20	Sch 5S	3	2	6	9/9/2020
DUPLEX HEADER	W1WRS01-BR006-0500-P3400-PER	W1WRS01-BR006-0500-P3400-PER-01	J-06	20	Sch 5S	3	3	7	9/11/2020
DUPLEX HEADER	W1WRS01-BR006-0500-P3400-PER	W1WRS01-BR006-0500-P3400-PER-01	J-06	20	Sch 5S	3	4	8	9/11/2020
DUPLEX ISO	W1WRS01-BR008-0150-P3400-BRN	W1WRS01-BR008-0150-P3400-BRN-05	7	2	Sch 40S	3	1	5	10/5/2020
DUPLEX ISO	W1WRS01-BR008-0150-P3400-BRN	W1WRS01-BR008-0150-P3400-BRN-05	8	2	Sch 40S	3	1	5	10/5/2020
DUPLEX ISO	W1WRS01-BR008-0150-P3400-BRN	W1WRS01-BR008-0150-P3400-BRN-05	9	2	Sch 40S	3	1	5	10/5/2020
DUPLEX ISO	W1WRS01-BR008-0150-P3400-BRN	W1WRS01-BR008-0150-P3400-BRN-05	10	2	Sch 40S	3	1	5	10/5/2020
DUPLEX ISO	W1WRS01-BR008-0150-P3400-BRN	W1WRS01-BR008-0150-P3400-BRN-06	11	2	Sch 40S	3	1	5	10/5/2020

[Anonymous]

@v-jingzhang : PFA data above for your reference

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Your measure works well in my test, however I create another measure which could reproduce your issue. Please refer to the following image first and check the difference between them.

The cause is that the dates in 'FITUP INCH DIA' table are not continuous, however dates in 'Dates' Table are continuous. When using 'FITUP INCH DIA'[Date] column in the measure, if the date does not exist in 'FITUP INCH DIA' table, then the reult of LASTDATE() will be blank as well as the DATESINPERIOD(), which makes the measure value blank. However, when using 'Dates'[Date] column in the measure, the date always exist in 'Dates' table so the  LASTDATE() and DATESINPERIOD() will not be blank, which makes the measure value not blank.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Anonymous]

Hi @v-jingzhang ,

This worked out well for me! Thanks for your time, appreciate it!!

[v-jingzhang]

@Anonymous You are welcome. Glad it worked!

[Anonymous]

Hey @amitchandak 

Can you help on the same?

[v-jingzhang]

@Anonymous 

Can you please share some sample data in table format as well as the measure codes for 7 days rolling average so that I can test it further? And is there any relationship between the tables? Maybe the filters in the matrix context are different and would affect this. Thanks.

Best Regards,
Community Support Team _ Jing Zhang

[Anonymous]

Hey Amit,

Attached screenshot showing the recurring values which aren't supposed to be filled in empty slots.

As you can see on the right table, that error isn't happening

[amitchandak]

@Anonymous , Can you please highlight the error, not very clear with the screenshot.