Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |