Measure to Create Datelist of Missing Dates

[BICrazy]

Hello there Power BI Community:

I would like to write a DAX measure that generates all the missing dates between the Inception and Expiry date for each Contract ID.  Note that it has to be a Measure as I cannot use a Calculated Table or Power Query Solution as this would increase my data model significantly.

Here is my dataset:

ContractID	Inception Date	Expiry Date
A105214	19/02/2022	19/02/2024
A105215	13/02/2023	13/02/2025

I've written the below DAX Measure and the output is correct, however, because of the CONCATENATEX function, all the results are in one row/cell.  

DateList = 

VAR StartDate = MIN('Data'[Inception Date])
VAR EndDate = MAX('Data'[Expiry Date])
VAR DateRange = GENERATESERIES(
    0,
    DATEDIFF(StartDate, EndDate, DAY),
    30
)
RETURN
    CONCATENATEX(
        DateRange,
        FORMAT(StartDate + [Value], "MMM-yyyy"),
        "  "
    )

I need the output to be in a format like this, with a measure.

Contract ID	DateList
A105214	Feb-22
A105214	Mar-22
A105214	Apr-22
A105214	May-22
A105214	Jun-22
A105214	Jul-22
A105214	Aug-22
A105214	Sep-22
A105214	Oct-22
A105214	Nov-22
A105214	Dec-22
A105214	Jan-23
A105214	Feb-23
A105214	Mar-23
A105214	Apr-23
A105214	May-23
A105214	Jun-23
A105214	Jul-23
A105214	Aug-23
A105214	Sep-23
A105214	Oct-23
A105214	Nov-23
A105214	Dec-23
A105214	Jan-24
A105214	Feb-24

Thanks in advance. 

[talespin · Accepted solution]

hi @BICrazy 

What is it that you want to achieve, as you said there is too much data, even if doing it with a measure, your visual will crash. If you can share what you are trying to do, maybe there is a better solution.

This involves a bit of workaround. 

1. Create a Date table

CALENDAR =

VAR _MinDate = MIN(Contract[InceptionDate])

VAR _MaxDate = MAX(Contract[ExpiryDate])

RETURN CALENDAR(_MinDate, _MaxDate)

2. Create this measure and add, ContractID, Date from Calendar table and below measure. Rename the measure to space and under formatting change text color to white and apply to both header and value.

AllDatesBetween =

VAR _ContractID = SELECTEDVALUE(Contract[ContractID])

VAR _CalDate = SELECTEDVALUE('CALENDAR'[Date])

VAR _InceptionDate = CALCULATE(  MAX(Contract[InceptionDate]) , REMOVEFILTERS(Contract), REMOVEFILTERS('CALENDAR'[Date]), VALUES(Contract[ContractID]) )

VAR _ExpiryDate = CALCULATE( MAX(Contract[ExpiryDate]) , REMOVEFILTERS(Contract), REMOVEFILTERS('CALENDAR'[Date]), VALUES(Contract[ContractID]) )

RETURN IF( _CalDate >= _InceptionDate && _CalDate <= _ExpiryDate, 1, BLANK())

[BICrazy]

Hi Talespin,  

Thank you so much much this approach.  It worked wonders.  I appreciate the time you've taken to explain it.
Some insights into my project. I'm developing a Cohort Matrix and will create an Index for each month since inception.

My visual will end up looking like this.

[talespin]

hi @BICrazy 

You're welcome.

Just throwing out idea from what I understand from the image above. I think all you need is a disconnected Date table(Min and Max dates derived from source table) to reflect all dates and measure which will do the calculation for "currrent inception date and Index"(which would be the filter context for your calculation).

[v-kongfanf-msft]

Hi @BICrazy ,

Using measure doesn't seem to achieve this.

The best way to do this is to split the string first in the power query and then utilize the unpivot column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXJLTdI1MjAyUvBNLIIwHAuKYCKVYIZSrE60UhJCrTFMrTFMrTFMrbFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BICrazy]

Hi Adam,

Thanks for trying to assist.  I already have a solution for Power Query that works.  But I'm trying to keep my dataset to a minimum therefore I decided to steer away from this option.

regards

[Dangar332]

Hi, @BICrazy 

don't sure but try below code for measure

Measure = 
SELECTCOLUMNS(
    GENERATE('Table',
       CALENDAR(MIN('Table'[Inception Date]),MIN('Table'[Expiry Date])
       )
    ),
    "t",
    [Date]
)

[BICrazy]

@Dangar332 Thanks for this, but it gives me an error as per the screenshot below.

You can also test it as is a small sample data (2 rows) that I provided in my original email.