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.
09-02-2022 08:03 AM - last edited 11-24-2022 01:07 AM
Data is given with Date Ranges(Start Date and End Date). And we need to provide the month start date and end date between those. For the first month, it should give the start date and for the last month, it should be the given end date.
The Sorce
Output needed
Power Query Code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLXNzTQNzIwMgKygSxDUwgnVidaCSRkBhcBsY0MELLGIA36cElzfWNDqGQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S N" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Start Date", each let
_s =[Start Date],
_q= List.Select( List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0)), each _ = List.Max({_s,Date.StartOfMonth(_)}))
in
_q),
#"Expanded New Start Date" = Table.ExpandListColumn(#"Added Custom", "New Start Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded New Start Date",{{"New Start Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "New End Date", each List.Min({[End Date], Date.EndOfMonth([New Start Date])}))
in
#"Added Custom1"
DAX Code
Distributed = Distinct(SELECTCOLUMNS(ADDCOLUMNS(GENERATE(Data, CALENDAR([Start Date],[End Date])), "New Start", max(eomonth([Date],-1)+1,[Start Date]) , "New End", Min(EOMONTH([Date],0), [End Date])),"S N",[S N], "Start Date", [Start Date], "End date", [End Date], "New Start Date", [New Start],"New End Date", [New End]))
Full Logic available at Medium -https://amitchandak.medium.com/power-bi-power-query-vs-dax-months-between-range-df019cec823b
Find the file below. Comment and let me know which one you liked.
Find all my Medium blogs here
Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share