Forum Discussion

Oleander's avatar
Oleander
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate a value from a schedule for a specifi week

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 . 

DepDataStart dateEnd date
11122022-01-012022-04-17
11222022-01-022022-04-17
11352022-01-032022-04-17
11452022-01-042022-04-17
11572022-01-052022-04-17
11672022-01-062022-04-17
11742022-01-072022-04-17
11842022-01-082022-04-17
11922022-01-092022-04-17
12022022-01-102022-04-17
11122022-04-182022-05-03
11222022-04-182022-05-03
11342022-04-182022-05-03
11442022-04-182022-05-03
11562022-04-182022-05-03
11662022-04-182022-05-03
11742022-04-182022-05-03
11842022-04-182022-05-03
11922022-04-182022-05-03
12022022-04-182022-05-03
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
    Oleander
    4 years ago

    Hi,

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

     

    //Göran

7 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

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

  • Oleander's avatar
    Oleander
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        Oleander
        Icon for Helper I rankHelper I
        4 years ago

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

        I will try this 

        //Göran