Extract month name from a date-range

[sandeshp]

I have a "Memberships" table which contains this data:

Membership start date.	Membership end date	Membership type
2024-01-15	2024-02-28	Type-1
2024-02-03	2024-03-20	Type-2
2023-10-21	null	Type-3

I want to create a bar chart that shows how many members had a valid membership each month, over a given date range.

The date-range will be used as a page-level filter. (I am using PowerBI embedded, so this is necessary)

The membership is valid in a given month if:

"Membership start date" <= "date range end"
AND
(
    "Membership end date" IS NULL
    OR
    "Membership end date" >= "Membership start date"
)

So if I pass the date range as: (2023-12-01 to 2024-04-30), the resulting data should be:

Membership start date	Membership end date	Membership type	Month Name
2024-01-15	2024-02-28	Type-1	Jan-24
2024-01-15	2024-02-28	Type-1	Feb-24
2024-02-03	2024-03-20	Type-2	Feb-24
2024-02-03	2024-03-20	Type-2	Mar-24
2023-10-21		Type-3	Dec-23
2023-10-21		Type-3	Jan-24
2023-10-21		Type-3	Feb-24
2023-10-21		Type-3	Mar-24
2023-10-21		Type-3	Apr-24

(Notice that the month names "Oct-23" and "Nov-23" should not be there since the passed date range starts from 2023-12-01)

I am thinking of creating a calendar table and CROSSJOIN it with the "Memberships" table, and filter the data afterwards, but that would create a huge new table in Power BI(Actual "Memberships" table would contain more than 250k rows).
I am also concerned about how it would refresh the data if the data from "Memberships" table refreshes.

How do I achieve this in Power BI?

(PowerBI file with the CROSSJOIN and FILTER approach: dropbox link)

[danextian · Accepted solution]

Hi @sandeshp ,

You can try the sample M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVNzDUNzIwMlHSUTKy0DcwgnFCKgtSdQ2VYnWilQyMkcSNDPSBfGRFRmBFRob6hgYgcWOgeF5pTg5M2lgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Membership start date" = _t, #"Membership end date" = _t, #"Membership type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Membership start date", type date}, {"Membership end date", type date}, {"Membership type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates + One Month", each let 
start = [Membership start date],
end = if [Membership end date] = null then Date.From(DateTime.LocalNow()) else [Membership end date],
Diff = Number.Round(Number.From((end - start)/( 365.25 / 12 )) ,0 )
in List.Transform({0..Diff}, each Date.AddMonths(start, _))),
    #"Expanded Dates + One Month" = Table.ExpandListColumn(#"Added Custom", "Dates + One Month"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Dates + One Month", "Period", each Date.ToText([#"Dates + One Month"], "MMM-yy")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Dates + One Month", type date}, {"Period", type text}})
in
    #"Changed Type1"

You can then filter the query based on your desired date range.