Calculate a value from a schedule for a specifi week

[Oleander]

HI,

I have a schedule like this for between specific dates and I want to show values for a Department  for a specific week how do I go about a measure to do that ? 

I have Calender table with all dates and responding weeks that I want to pick the week from and get the Data from each department . 

Dep	Data	Start date	End date
111	2	2022-01-01	2022-04-17
112	2	2022-01-02	2022-04-17
113	5	2022-01-03	2022-04-17
114	5	2022-01-04	2022-04-17
115	7	2022-01-05	2022-04-17
116	7	2022-01-06	2022-04-17
117	4	2022-01-07	2022-04-17
118	4	2022-01-08	2022-04-17
119	2	2022-01-09	2022-04-17
120	2	2022-01-10	2022-04-17
111	2	2022-04-18	2022-05-03
112	2	2022-04-18	2022-05-03
113	4	2022-04-18	2022-05-03
114	4	2022-04-18	2022-05-03
115	6	2022-04-18	2022-05-03
116	6	2022-04-18	2022-05-03
117	4	2022-04-18	2022-05-03
118	4	2022-04-18	2022-05-03
119	2	2022-04-18	2022-05-03
120	2	2022-04-18	2022-05-03

[v-kkf-msft · Accepted solution]

Hi @Oleander ,

Please add the following custom function to convert the date to ISO week number. 

Referencing: M functions to convert between ISO 8601 Week  

let
    getISO8601Week = (someDate as date) =>
        let
            getDayOfWeek = (d as date) =>
                let
                    result = 1 + Date.DayOfWeek(d, Day.Monday)
                in
                    result,

            getNaiveWeek = (inDate as date) =>
                let
                    // monday = 1, sunday = 7
                    weekday = getDayOfWeek(inDate),

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

                    ordinal = Date.DayOfYear(inDate),

                    naiveWeek = Number.RoundDown(
                        (ordinal - weekday + 10) / 7
                    )
                in
                    naiveWeek,

            thisYear = Date.Year(someDate),

            priorYear = thisYear - 1,

            nwn = getNaiveWeek(someDate),

            lastWeekOfPriorYear =
                getNaiveWeek(#date(priorYear, 12, 28)),

            // http://stackoverflow.com/a/34092382/2014893
            lastWeekOfThisYear =
                getNaiveWeek(#date(thisYear, 12, 28)),

            weekNumber =
                if
                    nwn < 1
                then
                    lastWeekOfPriorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        1
                    else
                        nwn 
        in
            weekNumber
in
    getISO8601Week

Then modify the formula of the custom column.

WeekList = {DateToISOWeek([Start date])..DateToISOWeek([End date])}

Best Regards,
Winniz

[Oleander]

I would like to get as a result if I choose a week 16 :

For example :

Week 16 

Dep  Data

111   2

112   2

113   4

114   4

115   6

116   6

117   4

118   4

119   2

120   2

[v-kkf-msft]

Hi @Oleander ,

1. If you want to use query parameter for filtering, then try the M code (in my calendar, week 17 is from 4/18/2022 to 4/24/2022):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBLCsAgDEXRvTiuYGL8raV0/9uombQ2PGlAB+pB5Z5nIKJwBNaZmGOiOZ6FRGrhOlSxVYxUnptlVRkpsUqQUtFWVZCqVlWkVMiqGlLdqo7UsCUGUJyMooTu+rafB++LReOh9juVP7/fKXEpTV1/VXWp5nqxu9TwlDDtgbpu", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dep = _t, Data = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dep", Int64.Type}, {"Data", Int64.Type}, {"Start date", type date}, {"End date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekList", each {Date.WeekOfYear([Start date], Day.Monday)..Date.WeekOfYear([End date], Day.Monday)}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IsContainSelectWeek", each List.Contains([WeekList], SelectWeekNum)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([IsContainSelectWeek] = true))
in
    #"Filtered Rows"

2. If you want to filter by the slicer, then you can try to create the measure and display the values in the visual-level filter where the measure is not blank.

SelectRows = 
COUNTROWS (
    FILTER (
        'Table (2)',
        'Table (2)'[Start date] <= MAX ( Calender[Dates] )
            && 'Table (2)'[End date] >= MIN ( Calender[Dates] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oleander]

Thank you so much, Winniz for your suggestion to a solution.

I will try this 

//Göran

[Oleander]

Hi there !

I got half way due to that I can get the week nr from your solution but as you say and a occuring issue is that our Week numbering is not the same .

If I use the ISOWeeknum weeknum),21 for us I get the week 16 from 2022-04-18 to 2022-04-24 . If I can solve this week calculation I'll be a lot closer to a solution. 

[v-kkf-msft · Accepted solution]

Hi @Oleander ,

Please add the following custom function to convert the date to ISO week number. 

Referencing: M functions to convert between ISO 8601 Week  

let
    getISO8601Week = (someDate as date) =>
        let
            getDayOfWeek = (d as date) =>
                let
                    result = 1 + Date.DayOfWeek(d, Day.Monday)
                in
                    result,

            getNaiveWeek = (inDate as date) =>
                let
                    // monday = 1, sunday = 7
                    weekday = getDayOfWeek(inDate),

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

                    ordinal = Date.DayOfYear(inDate),

                    naiveWeek = Number.RoundDown(
                        (ordinal - weekday + 10) / 7
                    )
                in
                    naiveWeek,

            thisYear = Date.Year(someDate),

            priorYear = thisYear - 1,

            nwn = getNaiveWeek(someDate),

            lastWeekOfPriorYear =
                getNaiveWeek(#date(priorYear, 12, 28)),

            // http://stackoverflow.com/a/34092382/2014893
            lastWeekOfThisYear =
                getNaiveWeek(#date(thisYear, 12, 28)),

            weekNumber =
                if
                    nwn < 1
                then
                    lastWeekOfPriorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        1
                    else
                        nwn 
        in
            weekNumber
in
    getISO8601Week

Then modify the formula of the custom column.

WeekList = {DateToISOWeek([Start date])..DateToISOWeek([End date])}

Best Regards,
Winniz

[Oleander · Accepted solution]

Hi,

This works though all dates must be in the same year.

//Göran

[Vijay_A_Verma]

If you can also give the example of possible output, it will be easier for experts here to provide a solution to you.