Forum Discussion
Issues with plotting two summed values in a line chart
kcngnkc The data within your Time and Fiscal Week columns need to match. You may notice that both measures (PT_ACT and PT_TGT) are showing high values for 'blank'
Is this what your Calendar table looks like in the actual file? It needs a bit of cleansing if so. Your Fiscal Week column should ONLY have Fiscal Week, but it also has 'FY19', "Year" and other values that don't fit the pattern.
Your Time column in the EEF table has the same problem. You need to cleanse the data first so that the Time and Period are consistent, matching and only contain 1 piece of information (ie Week 1, Week 2, Week 3, ...). The Fiscal Year should go in another column. Your Calendar table should have every date for all years, and you should use a date key of sorts to link the EEF table to the Calendar table. My recommendation would be having a 'Start of Week' column in the EEF table that is a datekey such as '20200517' (YYYYMMdd) and link that to the DateKey column in the Calendar table once you have gotten your calendar table cleansed and sorted.
Try this link for some help with a robust calendar table:
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Hi Allison, you have brought up a similar suggestion by JD. My "time" column in EFF table (which is comprised of fiscal weeks, fiscal period, etc) is linked to my calendar table's Fiscal Week column. The time column in the EFF table is also used for the PT_ACT, yet it's rcognizing it, so why is it not doing the same for PT_TGT?
kcngnkc The data within your Time and Fiscal Week columns need to match. You may notice that both measures (PT_ACT and PT_TGT) are showing high values for 'blank'
Is this what your Calendar table looks like in the actual file? It needs a bit of cleansing if so. Your Fiscal Week column should ONLY have Fiscal Week, but it also has 'FY19', "Year" and other values that don't fit the pattern.
Your Time column in the EEF table has the same problem. You need to cleanse the data first so that the Time and Period are consistent, matching and only contain 1 piece of information (ie Week 1, Week 2, Week 3, ...). The Fiscal Year should go in another column. Your Calendar table should have every date for all years, and you should use a date key of sorts to link the EEF table to the Calendar table. My recommendation would be having a 'Start of Week' column in the EEF table that is a datekey such as '20200517' (YYYYMMdd) and link that to the DateKey column in the Calendar table once you have gotten your calendar table cleansed and sorted.
Try this link for some help with a robust calendar table:
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.