cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## DAX for group by and datediff

Hi

i have a table with 2 columns

id   start_date

1     2017/05/06

2       ""

3       ""

4        ""

1    2018/06/05

1       ""

2        ""

Now I want to add a new column here which shows

a. The difference in start_date vs today ( I am able to achieve this using datediff function)

b. I want to apply group by to 'id' column and take the latest date in the start_date column. In the above example for id=1 and start_date = 2018/06/05.  Once I am able to group by id and get the latest start_date for that id then I would find diff of it with today's date.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: DAX for group by and datediff

Hi @rjain12,

Based on my test, you could refer to below formula:

Difference column:

`Difference = DATEDIFF('Table1'[start_date],TODAY(),DAY)`

Group by table:

`Group by table = SUMMARIZE('Table1','Table1'[id],"Latest",CALCULATE(MAX('Table1'[start_date])))`

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super Contributor

## Re: DAX for group by and datediff

Hi @rjain12,

Based on my test, you could refer to below formula:

Difference column:

`Difference = DATEDIFF('Table1'[start_date],TODAY(),DAY)`

Group by table:

`Group by table = SUMMARIZE('Table1','Table1'[id],"Latest",CALCULATE(MAX('Table1'[start_date])))`

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Contributor

## Re: DAX for group by and datediff

Hi @rjain12 ,

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 24 members 781 guests
Recent signins: