Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
BICrazy
Helper II
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:

ContractIDInception DateExpiry Date

A10521419/02/202219/02/2024
A10521513/02/202313/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"),
        "  "
    )

 

 

 


Output.png











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

Contract IDDateList
A105214Feb-22
A105214Mar-22
A105214Apr-22
A105214May-22
A105214Jun-22
A105214Jul-22
A105214Aug-22
A105214Sep-22
A105214Oct-22
A105214Nov-22
A105214Dec-22
A105214Jan-23
A105214Feb-23
A105214Mar-23
A105214Apr-23
A105214May-23
A105214Jun-23
A105214Jul-23
A105214Aug-23
A105214Sep-23
A105214Oct-23
A105214Nov-23
A105214Dec-23
A105214Jan-24
A105214Feb-24


Thanks in advance. 

1 ACCEPTED SOLUTION
talespin
Solution Sage
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())
 
talespin_0-1708499662974.png

 

View solution in original post

7 REPLIES 7
talespin
Solution Sage
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())
 
talespin_0-1708499662974.png

 

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.
Cohort.png


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
Community Support
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"

vkongfanfmsft_0-1708499233940.png

vkongfanfmsft_1-1708499281436.png

 

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.

 

 

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

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

error.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors