Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
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
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.
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).
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.
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |