cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dilumd
Solution Supplier
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_NameItem NoQuantityVersion_IDStartDateEnd Date
Apple PieABC001                  11101/01/2003/01/20
Apple PieABC002                  21101/01/2003/01/20
Apple PieABC003                     8101/01/2003/01/20
Apple PieABC004                  27101/01/2003/01/20
Apple PieABC005                     6101/01/2003/01/20
Apple PieABC006                     5101/01/2003/01/20
Apple PieABC001                  27203/01/2007/01/20
Apple PieABC002                  22203/01/2007/01/20
Apple PieABC003                     8203/01/2007/01/20
Apple PieABC004                     8203/01/2007/01/20
Apple PieABC005                     6203/01/2007/01/20
Apple PieABC006                     5203/01/2007/01/20
Apple PieABC001                  21307/01/20Today()
Apple PieABC002                  27307/01/20Today()
Apple PieABC003                  11307/01/20Today()
Apple PieABC004                     8307/01/20Today()
Apple PieABC005                     6307/01/20Today()
2 ACCEPTED SOLUTIONS
v-janeyg-msft
Community Support
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’.

v-janeyg-msft_1-1603070606942.png

If it doesn’t solve your problem, please feel free to ask me.

 

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.

View solution in original post

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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
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’.

v-janeyg-msft_1-1603070606942.png

If it doesn’t solve your problem, please feel free to ask me.

 

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.

amitchandak
Super User
Super User

@dilumd ,

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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.

 

Issue.PNG

 

Added more data,

BOM_NameItem NoQuantityVersion_IDStartDateEnd Date

Apple PieABC001                  1111/1/20203/1/2020
Apple PieABC002                  2111/1/20203/1/2020
Apple PieABC003                     811/1/20203/1/2020
Apple PieABC004                  2711/1/20203/1/2020
Apple PieABC005                     611/1/20203/1/2020
Apple PieABC006                     511/1/20203/1/2020
Apple PieABC001                  2723/1/20207/1/2020
Apple PieABC002                  2223/1/20207/1/2020
Apple PieABC003                     823/1/20207/1/2020
Apple PieABC004                     823/1/20207/1/2020
Apple PieABC005                     623/1/20207/1/2020
Apple PieABC006                     523/1/20207/1/2020
Apple PieABC001                  2137/1/2020Today()
Apple PieABC002                  2737/1/2020Today()
Apple PieABC003                  1137/1/2020Today()
Apple PieABC004                     837/1/2020Today()
Apple PieABC005                     637/1/2020Today()
CakeABC001                  1111/1/20206/1/2020
CakeABC002                  2111/1/20206/1/2020
CakeABC003                     811/1/20206/1/2020
CakeABC004                  2711/1/20206/1/2020
CakeABC005                     611/1/20206/1/2020
CakeABC006                     511/1/20206/1/2020
CakeABC001                  2726/1/2020Today()
CakeABC002                  2226/1/2020Today()
CakeABC003                     826/1/2020Today()
CakeABC004                     826/1/2020Today()
CakeABC005                     626/1/2020Today()
CakeABC006                     526/1/2020Today()

 

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors