Forum Discussion
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.
Please help
Hi Anonymous,
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
3 Replies
Hi Anonymous,
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi Anonymous ,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?
Regards,
Daniel He
I have a similar issue, but still struggling with the solution provided. I am trying to add a DATEDIFF column to a table visual with an aggregate column (count of tickets). The DATEDIFF calc works if I exclude the # of Tickets aggregate column.
DaysLate = DATEDIFF(Tkscale[TicketDate], Today(),DAY)This is what I expect to see:TicketDate # of Tickets DaysLate 02/25/20 3 1 02/25/20 14 1 02/25/20 62 1 This is what I currently see:
TicketDate # of Tickets DaysLate 02/25/20 3 3 02/25/20 14 14 02/25/20 62 62 What should the calculation look like?
Thank you,
Cyleste
