Function to calculate per week

[aTChris]

Hi everyone, I hope you are all well.

I have an amazing function created by @Mariusz . It calculates a monthly pro rated amount that I apply to a monthly revenue value. I then visualize that amount in a matrix.

I'm trying to update the function to report weekly and struggling. Can anyone help?

( startDate as date, endDate as date ) => let
    sYear = Date.Year( startDate ),
    sDate = #date( sYear, Date.Month( startDate ), 1 ),
    eYear = Date.Year( endDate ),
    eDate = Date.EndOfMonth( #date( eYear, Date.Month( endDate ), 1 ) ),
    listYears = List.Transform( { sYear..eYear }, each { _ } ),
    listToTable = #table( type table [ #"Years" = Int64.Type ], listYears ),
    addMonths = Table.ExpandListColumn( Table.AddColumn( listToTable, "Months", each { 1..12 } ) , "Months" ),
    getStartDate = Table.AddColumn( addMonths, "startDate", each #date( [Years], [Months], 1 ) )[ startDate ],
    selectStartDate = List.Select( getStartDate, each (_) >= sDate and (_) <= eDate ), 
    output = List.Transform( selectStartDate, 
        each 
        Record.FromList(
            {   
                ( Number.From( 
                    Duration.From( 
                        ( if Date.EndOfMonth( _ ) > endDate then endDate else Date.EndOfMonth( _ ) ) - 
                        ( if ( _ ) < startDate then startDate else ( _ ) ) 
                    ) 
                ) + 1 ) / ( Number.From( Duration.From( Date.EndOfMonth( _ ) - ( _ ) ) ) + 1 ) 
                , _ 
            }, type [pct = number, firstDate = date]  

        ) 
    )  
in  output

Thanks

Chris

[Mariusz · Accepted solution]

Hi @aTChris 

Try this

( startDate as date, endDate as date ) => let
    dates = List.Dates( startDate, Duration.Days( endDate - startDate) + 1, #duration( 1, 0, 0, 0 ) ),
    transform = List.Transform( dates, each { _, ( Date.Year( _ ) * 100 ) + Date.WeekOfYear( _ ) } ),
    tableFrom = #table( type table [date= date, yearWeek = number ], transform ),
    group = Table.Group( tableFrom, {"yearWeek"}, {{"pct", each Table.RowCount(_) / 7, type number}}) 
in  group

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[aTChris]

@Mariusz 

Perfect, thank you.

For anyone else I had to create a YearWeek col in my calendar table which wasn't as simple as id hoped. I added a custom col using the following to create the week number then merged with the year.

Text.PadStart(Text.From([Week of Year]),2,"0")