Power BI- Power Query Vs DAX — Months Between Range

Problem Statement: Data is given with Date Range(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 Source Data is give below.

Source Between Range Data

Output needed

Month Start and End Date between a range

Power Query Solution

In Power Query we have List.Dates to generate all the dates. List.Select can help in filtering the start dates using Date.StartOfMonth. But for the first month we need the max of start of the month or the original start date. Problem is that, when we use List.Select on top List.Dates in a calculated column, we can not access the start date to compare in List.Select function

In a new column — List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0))

Now use List.Select , we need max of month start date or Start Date- List.Select( List.Dates([Start Date], Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0)), each _ = List.Max({[Start Date],Date.StartOfMonth(_)}))

[Start Date] will give an error here. This forces to create a variable in a column first, to the final column formula is

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

So the variable allows us to compare two dates.

We got the list dates, which we can expand, using the expand icon on the right of the column, “New Start Date”, highlighted in yellow.

Power Query Expand the Date

We got the start dates

Start Date of Month

Now for the end date, add the following calculated column

List.Min({[End Date], Date.EndOfMonth([New Start Date])})

Created a List of End Date and month end date and took a minimum

Final Table

Full 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 Solution

In Dax, we need to create a new table. Generate with Calendar will give all dates between range — GENERATE(Data, CALENDAR([Start Date],[End Date]))

Now with help from “addcolumns” we can add columns “New Start Date” and “New End Date”. EOMONTH will help to get the month start date and the month end date; and Min/Max will help to settle the date logic: ADDCOLUMNS(GENERATE(Data, CALENDAR([Start Date],[End Date])), “New Start”, max(eomonth([Date],-1)+1,[Start Date]) , “New End”, Min(EOMONTH([Date],0)

Selectcolumns and distinct will help to remove unnecessary date column and duplicate rows.

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

The final outcome is

DAX- Month Range Between Range

You can find the file here. Comment and let me know which one you liked.

You can refer to the videos for the List.Dates, List.Select, List.Min and List.Max on my YouTube Channel

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