Forum Discussion

aTChris's avatar
aTChris
Icon for Resolver I rankResolver I
6 years ago
Solved

Function to calculate per week

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 amoun...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
aTChris
Icon for Resolver I rankResolver I
6 years ago

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")