none
working days between two dates by subtracting holidays RRS feed

  • Question

  • working days between two dates by subtracting holidays
    I found the forum a function, but I can not do.
    I did it

    .........................................

    let
      
    Table.RowCount (
          
    Table.SelectRows (
            
    DimDate
            
    , each
              
    and [WorkDayFlag] = true)
          
    )
        
    )

        
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzDUNzIwNFPSUTI0gnNidYBypshyJihyRoZIcsYGCLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) goal [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        
    # "Changed type" = Table.TransformColumnTypes (Source, {{"Column1", type date}, {"Column2", type date}})
        
    # "Columns with Name Changed" = Table.RenameColumns (# "type changed," {{"Column1", "start"}, {"Column2", "end"}})
        
    # "Added consultation" Table.Combine = ({# "Columns with Name Changed" consultation3})
        
    # "Columns with Mudado1 Name" = Table.RenameColumns (# "Query added," {{"Column1", "holidays"}})
        
    # "Custom Added" = Table.AddColumn (# "columns with Mudado1 Name", "Custom", each WorkDaysBetween (Date.From ([start]), Date.From ([end]), date.from ([holidays] )))
    in
        
    # "Custom Added"

    ...............................................

    but it did not work

    My table

     dates

    start end
    25/01/2016    26/02/2016
    26/12/2015 15/01/2016
    15/12/2015 25/01/2016

    holidays

    holidays
    01/01/2016
    24/12/2015
    25/12/2015

     want to show the value formatted in days



    • Edited by Goodman_ti Wednesday, February 3, 2016 4:12 PM
    Wednesday, February 3, 2016 4:03 PM

Answers

All replies