Forum Discussion
Need help with Table Visualization error in PowerBI desktop
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.
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.
9 Replies
Anonymous , Can you please highlight the error, not very clear with the screenshot.
Hey amitchandak
Can you help on the same?
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
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))/7Below 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 v-jingzhang : PFA data above for your reference
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.
