cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Solution Supplier

## next version start date as the end of the current version DAX Column

Hi Everyone,

I need to create a DAX column based on the next version start date. see the below data I need to create End Date column. if the next version is not available need to take today's date as the end date.

Thank you very much for your time.

 BOM_Name Item No Quantity Version_ID StartDate End Date Apple Pie ABC001 11 1 01/01/20 03/01/20 Apple Pie ABC002 21 1 01/01/20 03/01/20 Apple Pie ABC003 8 1 01/01/20 03/01/20 Apple Pie ABC004 27 1 01/01/20 03/01/20 Apple Pie ABC005 6 1 01/01/20 03/01/20 Apple Pie ABC006 5 1 01/01/20 03/01/20 Apple Pie ABC001 27 2 03/01/20 07/01/20 Apple Pie ABC002 22 2 03/01/20 07/01/20 Apple Pie ABC003 8 2 03/01/20 07/01/20 Apple Pie ABC004 8 2 03/01/20 07/01/20 Apple Pie ABC005 6 2 03/01/20 07/01/20 Apple Pie ABC006 5 2 03/01/20 07/01/20 Apple Pie ABC001 21 3 07/01/20 Today() Apple Pie ABC002 27 3 07/01/20 Today() Apple Pie ABC003 11 3 07/01/20 Today() Apple Pie ABC004 8 3 07/01/20 Today() Apple Pie ABC005 6 3 07/01/20 Today()
2 ACCEPTED SOLUTIONS
Community Support

Hi， @dilumd

It’s my pleasure to answer for you.

According to your description, I think you can create a measure to calculate the correct end date.

Like this:

Measure:

``````end date =
VAR a =
MAXX (
FILTER (
ALL ( Table2 ),
[item] = SELECTEDVALUE ( Table2[item] )
&& [name] = SELECTEDVALUE ( Table2[name] )
&& [version-id]
= SELECTEDVALUE ( Table2[version-id] ) + 1
),
Table2[startdate]
)
RETURN
IF ( a = BLANK (), TODAY (), a )``````

Or if you want use calculated column,please change ‘selectedvalue’ to ‘earlier’.

Best Regards

Janey Guo

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

Super User

Hi,

This calculated column works

``=if(ISBLANK(CALCULATE(MIN(Data[Start Date]),FILTER(Data,Data[BOM_Name]=EARLIER(Data[BOM_Name])&&Data[Item No]=EARLIER(Data[Item No])&&Data[Start Date]>EARLIER(Data[Start Date])))),today(),CALCULATE(MIN(Data[Start Date]),FILTER(Data,Data[BOM_Name]=EARLIER(Data[BOM_Name])&&Data[Item No]=EARLIER(Data[Item No])&&Data[Start Date]>EARLIER(Data[Start Date]))))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Community Support

Hi， @dilumd

It’s my pleasure to answer for you.

According to your description, I think you can create a measure to calculate the correct end date.

Like this:

Measure:

``````end date =
VAR a =
MAXX (
FILTER (
ALL ( Table2 ),
[item] = SELECTEDVALUE ( Table2[item] )
&& [name] = SELECTEDVALUE ( Table2[name] )
&& [version-id]
= SELECTEDVALUE ( Table2[version-id] ) + 1
),
Table2[startdate]
)
RETURN
IF ( a = BLANK (), TODAY (), a )``````

Or if you want use calculated column,please change ‘selectedvalue’ to ‘earlier’.

Best Regards

Janey Guo

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

Super User

Try like

coalesce(Minx(filter(table, [Version_ID] > earlier([Version_ID])+1 ),[StartDate]), today())

coalesce(Minx(filter(table, [Version_ID] > earlier([Version_ID])+1 ),earlier([StartDate])), today())

Solution Supplier

Thank you @amitchandak

However, with that formula, I'm getting the below answer which is not what I need. maybe I'll add more data for the clarity.

BOM_NameItem NoQuantityVersion_IDStartDateEnd Date

 Apple Pie ABC001 11 1 1/1/2020 3/1/2020 Apple Pie ABC002 21 1 1/1/2020 3/1/2020 Apple Pie ABC003 8 1 1/1/2020 3/1/2020 Apple Pie ABC004 27 1 1/1/2020 3/1/2020 Apple Pie ABC005 6 1 1/1/2020 3/1/2020 Apple Pie ABC006 5 1 1/1/2020 3/1/2020 Apple Pie ABC001 27 2 3/1/2020 7/1/2020 Apple Pie ABC002 22 2 3/1/2020 7/1/2020 Apple Pie ABC003 8 2 3/1/2020 7/1/2020 Apple Pie ABC004 8 2 3/1/2020 7/1/2020 Apple Pie ABC005 6 2 3/1/2020 7/1/2020 Apple Pie ABC006 5 2 3/1/2020 7/1/2020 Apple Pie ABC001 21 3 7/1/2020 Today() Apple Pie ABC002 27 3 7/1/2020 Today() Apple Pie ABC003 11 3 7/1/2020 Today() Apple Pie ABC004 8 3 7/1/2020 Today() Apple Pie ABC005 6 3 7/1/2020 Today() Cake ABC001 11 1 1/1/2020 6/1/2020 Cake ABC002 21 1 1/1/2020 6/1/2020 Cake ABC003 8 1 1/1/2020 6/1/2020 Cake ABC004 27 1 1/1/2020 6/1/2020 Cake ABC005 6 1 1/1/2020 6/1/2020 Cake ABC006 5 1 1/1/2020 6/1/2020 Cake ABC001 27 2 6/1/2020 Today() Cake ABC002 22 2 6/1/2020 Today() Cake ABC003 8 2 6/1/2020 Today() Cake ABC004 8 2 6/1/2020 Today() Cake ABC005 6 2 6/1/2020 Today() Cake ABC006 5 2 6/1/2020 Today()

Super User

Hi,

This calculated column works

``=if(ISBLANK(CALCULATE(MIN(Data[Start Date]),FILTER(Data,Data[BOM_Name]=EARLIER(Data[BOM_Name])&&Data[Item No]=EARLIER(Data[Item No])&&Data[Start Date]>EARLIER(Data[Start Date])))),today(),CALCULATE(MIN(Data[Start Date]),FILTER(Data,Data[BOM_Name]=EARLIER(Data[BOM_Name])&&Data[Item No]=EARLIER(Data[Item No])&&Data[Start Date]>EARLIER(Data[Start Date]))))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors