Forum Discussion
Need M code for dynamically selecting financial year start and end date picked from a slicer visual
I am trying something like the below in the Advanced editor. Replaced the highlighted part with code (again highlighted).
parameters: FyStartDate (Date/Time) : 4/3/2023 and FyEndDate (Date/Time): 3/31/2024
let
Source = Oracle.Database("192.168.5.202:1521/PROD", [HierarchicalNavigation=true, Query="SELECT#(lf) yy.sdu_name AS orgname,#(lf) to_char(yy.week_ending_date, 'dd-Mon-yy') AS week,#(lf) COUNT(*) AS headcount#(lf)FROM#(lf) (#(lf) SELECT#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date,#(lf) billable_hours,#(lf) normal_hours,#(lf) base_hours,#(lf) vacation_hours,#(lf) other_hours,#(lf) sdf_hours,#(lf) qip_hours,#(lf) SUM(spqt_hours_wk)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name, employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) AS spqt_hours,#(lf) CASE#(lf) WHEN SUM(base_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name, employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) < 0.9 THEN#(lf) 100#(lf) ELSE#(lf) SUM(billable_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) / nullif(SUM(base_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ),#(lf) 0) * 100#(lf) END AS ytd_ar,#(lf) CASE#(lf) WHEN SUM(base_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name, employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) < 0.9#(lf) OR ( SUM(base_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name, employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) - SUM(vacation_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) ) < 0.000001 THEN#(lf) 100#(lf) ELSE#(lf) SUM(billable_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) / ( SUM(base_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) - SUM(vacation_hours)#(lf) OVER(#(lf) ORDER BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date ASC#(lf) ROWS 3 PRECEDING#(lf) ) ) * 100#(lf) END AS ytd_yield#(lf) FROM#(lf) (#(lf) SELECT#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date,#(lf) SUM(billable_hours) AS billable_hours,#(lf) SUM(normal_hours) AS normal_hours,#(lf) SUM(base_hours) AS base_hours,#(lf) SUM(vacation_hours) AS vacation_hours,#(lf) SUM(nbh) AS other_hours,#(lf) SUM(sdf_hours) AS sdf_hours,#(lf) SUM(qip_hours) AS qip_hours,#(lf) SUM(sdf_hours + pip_hours + qip_hours + ted_hours) AS spqt_hours_wk#(lf) FROM#(lf) xxvdb.xxvdb_tmst_weekly_t#(lf) WHERE#(lf) dl_or_il = 'DL'#(lf) AND substr(band, 1, 1) IN ( 'E', '1', '2', 'N' )#(lf) AND joining_date < ( sysdate - 32 )#(lf) AND resignation_date IS NULL#(lf) AND normal_hours IS NOT NULL#(lf) AND normal_hours > 0.01#(lf) AND sdu_name IS NOT NULL#(lf) AND week_ending_date >= to_date('" & DateTime.ToText(FyStartDate,"dd-mm-yyyy") & "') - 20 AND week_ending_date < to_date('" & DateTime.ToText(Date.AddDays(FyEndDate,1),"dd-mm-yyyy") & "')#(lf) GROUP BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date#(lf) )#(lf) GROUP BY#(lf) sdu_name,#(lf) employee_number,#(lf) week_ending_date,#(lf) billable_hours,#(lf) normal_hours,#(lf) base_hours,#(lf) vacation_hours,#(lf) other_hours,#(lf) sdf_hours,#(lf) qip_hours,#(lf) spqt_hours_wk#(lf) ) yy#(lf) INNER JOIN xxvdb.xxvdb_hc_weekly hc ON hc.employee_number = yy.employee_number#(lf) AND hc.week_ending_date = yy.week_ending_date#(lf) AND hc.sdu_name = yy.sdu_name#(lf)WHERE #(lf) yy.week_ending_date >= to_date('" & DateTime.ToText(FyStartDate,"dd-mm-yyyy") & "') AND yy.week_ending_date < to_date('" & DateTime.ToText(Date.AddDays(FyEndDate,1),"dd-mm-yyyy") & "')#(lf) AND yy.ytd_yield < 80#(lf) AND ( billable_hours < 30#(lf) AND spqt_hours < ( 0.3 * normal_hours ) )#(lf) AND hc.sdu_name IN ( 'SDU1', 'SDU2', 'SDU4' )#(lf)GROUP BY#(lf) yy.sdu_name,#(lf) yy.week_ending_date#(lf)ORDER BY#(lf) yy.sdu_name,#(lf) yy.week_ending_date"])
in
Source
- Greg_Deckler3 years agoCommunity Champion
deepakramamurth Might have better luck with ImkeF edhans BA_Pete