Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Syntax for Rolling Average:
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 |
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.
Hi @v-jingzhang ,
This worked out well for me! Thanks for your time, appreciate it!!
@Anonymous You are welcome. Glad it worked!
@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
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
@Anonymous , Can you please highlight the error, not very clear with the screenshot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |