Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kagy100
Advocate II
Advocate II

DAX Assistance Needed

Hi Team,

 

I am hoping to seek your assistance with the following issue . . 

 

I have a date table set up with these dates:

16/03/2021

16/02/2021

19/01/2021

15/12/2020

17/11/2020

 

My desired output is required in 2 columns:

Start Date       End Date

16/02/2021    16/03/2021

19/01/2021    16/02/2021

15/12/2020    19/01/2021

17/11/2020    15/12/2020

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @kagy100 

Please check the below picture and the sample pbix file's link down below.

Those are for creating new columns.

 

Picture6.png

 

Start date CC =
CALCULATE (
MAX ( 'Table'[Column1] ),
FILTER ( 'Table', 'Table'[Column1] < EARLIER ( 'Table'[Column1] ) )
)

 

 

End date CC =
IF( not ISBLANK( 'Table'[Start date CC]), 'Table'[Column1])
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi, @kagy100 

Please check the below picture and the sample pbix file's link down below.

Those are for creating new columns.

 

Picture6.png

 

Start date CC =
CALCULATE (
MAX ( 'Table'[Column1] ),
FILTER ( 'Table', 'Table'[Column1] < EARLIER ( 'Table'[Column1] ) )
)

 

 

End date CC =
IF( not ISBLANK( 'Table'[Start date CC]), 'Table'[Column1])
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank You Jihwan

 

I encountered the following issue . . .

 

Date

21/04/2021

16/03/2021

16/02/2021

19/01/2021

15/12/2021

17/11/2021

 

Start Date CC gives me                   End Date CC gives me

20/04/2021                                      21/04/2021

15/03/2021                                      16/03/2021

15/02/2021                                      16/02/2021

18/01/2021                                      19/01/2020

14/12/2020                                      15/12/2020

16/11/2020                                      17/11/2020

 

Formula Used

Start Date CC =
CALCULATE (
MAX ( 'Table 1'[Date] ),
FILTER ( 'Table 1', 'Table 1'[Date] < EARLIER ( 'Table 1'Date] ) )
)
 
End Date CC =
IF( not ISBLANK( 'Table 1'[Start Date CC]), 'Table 1'[Date])
 
Would you know why I'm getting this? 

 

Hi, @kagy100 

Thank you for your feedback.

I am not sure why the unshown date is showing on your startdate column.

I think you connected the table to your dim-date table (or perhaps other table), but you are not showing it here.

 

Please share your sample pbix file's link here, and also share ideas about how you want to see it as an outcome.

 

Thank you.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@kagy100 , What is the logic for End Date ?

Hi Amit,

 

End Date must be the latest date and Start Date must be the earliest. 

 

For example:

Date

07/05/2021

05/03/2021

02/01/2021

05/11/2020

 

Output

Start Date            End Date

05/03/2021         07/05/2021

02/01/2021         05/03/2021

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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