cancel
Showing results for
Did you mean:

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.

Helper II

Measure to Create Datelist of Missing Dates

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

1 ACCEPTED SOLUTION
Solution Sage

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

7 REPLIES 7
Solution Sage

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

Helper II

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.

Solution Sage

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

Community Support

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,

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

Helper II

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

Super User

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]
)``````
Helper II

@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.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors