Need M code for dynamically selecting financial year start and end date picked from a slicer visual

[deepakramamurth]

Hi all, below is my SQL query. I have highlighted the content that needs to fetch information directly from the slicer selection of a visual. Tried for many hours but still dont want to give up.

SELECT
yy.sdu_name AS orgname,
to_char(yy.week_ending_date, 'dd-Mon-yy') AS week,
COUNT(*) AS headcount
FROM
(
SELECT
sdu_name,
employee_number,
week_ending_date,
billable_hours,
normal_hours,
base_hours,
vacation_hours,
other_hours,
sdf_hours,
qip_hours,
SUM(spqt_hours_wk)
OVER(
ORDER BY
sdu_name, employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) AS spqt_hours,
CASE
WHEN SUM(base_hours)
OVER(
ORDER BY
sdu_name, employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) < 0.9 THEN
100
ELSE
SUM(billable_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) / nullif(SUM(base_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
),
0) * 100
END AS ytd_ar,
CASE
WHEN SUM(base_hours)
OVER(
ORDER BY
sdu_name, employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) < 0.9
OR ( SUM(base_hours)
OVER(
ORDER BY
sdu_name, employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) - SUM(vacation_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) ) < 0.000001 THEN
100
ELSE
SUM(billable_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) / ( SUM(base_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) - SUM(vacation_hours)
OVER(
ORDER BY
sdu_name,
employee_number,
week_ending_date ASC
ROWS 3 PRECEDING
) ) * 100
END AS ytd_yield
FROM
(
SELECT
sdu_name,
employee_number,
week_ending_date,
SUM(billable_hours) AS billable_hours,
SUM(normal_hours) AS normal_hours,
SUM(base_hours) AS base_hours,
SUM(vacation_hours) AS vacation_hours,
SUM(nbh) AS other_hours,
SUM(sdf_hours) AS sdf_hours,
SUM(qip_hours) AS qip_hours,
SUM(sdf_hours + pip_hours + qip_hours + ted_hours) AS spqt_hours_wk
FROM
xxvdb.xxvdb_tmst_weekly_t
WHERE
dl_or_il = 'DL'
AND substr(band, 1, 1) IN ( 'E', '1', '2', 'N' )
AND joining_date < ( sysdate - 32 )
AND resignation_date IS NULL
AND normal_hours IS NOT NULL
AND normal_hours > 0.01
AND sdu_name IS NOT NULL
AND week_ending_date BETWEEN to_date('03-APR-2023') - 20 AND to_date('31-MAR-2024')
GROUP BY
sdu_name,
employee_number,
week_ending_date
)
GROUP BY
sdu_name,
employee_number,
week_ending_date,
billable_hours,
normal_hours,
base_hours,
vacation_hours,
other_hours,
sdf_hours,
qip_hours,
spqt_hours_wk
) yy
INNER JOIN xxvdb.xxvdb_hc_weekly hc ON hc.employee_number = yy.employee_number
AND hc.week_ending_date = yy.week_ending_date
AND hc.sdu_name = yy.sdu_name
WHERE
yy.week_ending_date BETWEEN to_date('03-APR-2023') AND to_date('31-MAR-2024')
AND yy.ytd_yield < 80
AND ( billable_hours < 30
AND spqt_hours < ( 0.3 * normal_hours ) )
AND hc.sdu_name IN ( 'SDU1', 'SDU2', 'SDU4' )
GROUP BY
yy.sdu_name,
yy.week_ending_date
ORDER BY
yy.sdu_name,
yy.week_ending_date

[deepakramamurth]

I got rid of the error. It was a simple format issue. However, the binding parameter concept is still not working as expected. The data is not getting filtered for the previous financial years. Any suggestings?

[deepakramamurth]

@MarcelBeug 

[edhans]

M code cannot dynamically pick values for slicers. 

What you can do is add a new column to your table, and using M code have the field be the same values as your slicer values, except for the value you want to be the one always selected - and you call that "Current" - then you pick Current in the slicer and publish.

Today, June 6 is "Current" when it refreshes. Tomorrow it is June 7, and so on.

You can also do this for Current week, month, year, whatever. 

For the day, the following code would work:
each if [Date] = DateTime.Date(DateTime.LocalNow()) then "Current" else Text.From([Date])

It returns this. You would have text values in the slicer, but it shouldn't matter. The true date is there and you can still get that via SELECTEDVALUE(SlicerTable[Date]) to work with as a true date.

You could even add a sort by column so Current was always at the top of the slicer.

each if [Slicer Date Value] = "Current" then 1 else Number.From([Date])

The numbers from the date would be those in the 45K range (days from Jan 1, 1900) and current would be 1. So all dates would be in the correct order, just with Current at the top.

[deepakramamurth]

@edhans Actually, it is the other way around. The slicer selection should pass the values to the m code which in return fetches the data from oracle only for the specific financial year start and end dates.

[edhans]

Ok, sorry. I don't spend much time trying to optimize Direct Query to relational databases. Too many downsides for the overwhelming number of my projects. Someone else with more experience in that might want to jump in.

[deepakramamurth]

With the above code, I get the below error. 

DataSource.Error: Oracle: ORA-01843: not a valid month
Details:
DataSourceKind=Oracle
DataSourcePath=192.168.5.202:1521/prod
Message=ORA-01843: not a valid month
ErrorCode=-2147467259

[deepakramamurth]

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

[deepakramamurth]

@Greg_Deckler  @amitchandak 

[Greg_Deckler]

@deepakramamurth Might have better luck with @ImkeF @edhans @BA_Pete