Excel BI Challenge Workout 199

Workouts Excel BI Challenge
21

LinkedIn Post by:
John Jairo V.

Hi to all!

:white_check_mark:`

One option could be:

=MAKEARRAY(ROWS(A2:B6),3,LAMBDA(r,c,LET(t,INDEX(A2:A6,r),s,SEQUENCE(1+INDEX(B2:B6,r)-t,t),d,FILTER(s,DAY(s)+MONTH(s)=–TEXT(s,“y”)),CHOOSE(c,ROWS(d),@d,MAX(d)))))

`

Blessings!

22

Power Query

let
	Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Record = Table.AddColumn (
		Source,
		"R",
		each [
			L = List.Dates (
				Date.From ( [From Date] ),
				Number.From ( [To Date] - [From Date] ) + 1,
				#duration ( 1, 0, 0, 0 )
			),
			S = List.Select ( L, ( f ) => Date.Month ( f ) + Date.Day ( f ) + 2000 = Date.Year ( f ) ),
			R = [ Count = List.Count ( S ), Min = List.Min ( S ), Max = List.Max ( S ) ]
		][R]
	),
	Return = Table.FromRecords ( Record[R] )
in
	Return

Month & Day Sum Equal to Year.xlsx (23.2 KB)