Forum Discussion
Merge the values into one
Hi Prasad23 ,
You can try below calculated column expression.
Result 1 = VAR TempTable = CALCULATETABLE( ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)), ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu]) ) VAR MinDiff = MINX(TempTable,[diff]) VAR MaxDiff = MAXX(TempTable,[diff]) RETURN IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days")Or is another result what you want?
Result 2 = VAR TempTable = CALCULATETABLE( ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)), ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu]) ) VAR MinDiff = MINX(TempTable,[diff]) VAR MaxDiff = MAXX(TempTable,[diff]) VAR Result = IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days") RETURN IF( ROWNUMBER( ALL('Table'[BU],'Table'[Sub_bu],'Table'[created date],'Table'[closed date]), ORDERBY('Table'[created date],ASC,'Table'[closed date],ASC), PARTITIONBY('Table'[BU],'Table'[Sub_bu]) )=1, Result )Demo - Merge the values into one.pbix
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
mark_endicott this is sample data,
| BU | Sub_bu | Description | created date | closed date |
| Service | Service1_ | Service 1&2 | 5/10/2024 | 5/20/2024 |
| Service | Service1_ | dummy | 5/10/2024 | 5/22/2024 |
| Service | Service1_ | approc | 5/11/2024 | 5/27/2024 |
| Service | Service2_ | need 2 ser | 5/9/2024 | 5/23/2024 |
| Service | service3_ | seedand plants | 5/13/2024 | 5/24/2024 |
| Service | service3_ | flowwers | 5/15/2024 | 5/22/2024 |
we need to calculate date difference between Created and Closed date.
i used the
ageing = var a = datediff(Closed date,Created date,day)
return a&" days"
I got like this,
| BU | Sub_bu | created date | closed date | Ageing |
| Service | Service1_ | 5/10/2024 | 5/20/2024 | 10 Days |
| Service | Service1_ | 5/10/2024 | 5/22/2024 | 12 Days |
| Service | Service1_ | 5/11/2024 | 5/27/2024 | 16 Days |
| Service | Service2_ | 5/9/2024 | 5/23/2024 | 14 Days |
| Service | service3_ | 5/13/2024 | 5/24/2024 | 11 Days |
| Service | service3_ | 5/15/2024 | 5/22/2024 | 7 Days |
but i required , for su_bu service1 the ageing should be 10-16 days.....
| BU | Sub_bu | created date | closed date | Result |
| Service | Service1_ | 5/10/2024 | 5/20/2024 | 10-16 days |
| Service | Service1_ | 5/10/2024 | 5/22/2024 | |
| Service | Service1_ | 5/11/2024 | 5/27/2024 | |
| Service | Service2_ | 5/9/2024 | 5/23/2024 | 14 days |
| Service | service3_ | 5/13/2024 | 5/24/2024 | 7-11 days |
| Service | service3_ | 5/15/2024 | 5/22/2024 | |
Please suggest in dax.
regards,
Prasad.
Hi Prasad23 ,
You can try below calculated column expression.
Result 1 =
VAR TempTable =
CALCULATETABLE(
ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)),
ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu])
)
VAR MinDiff = MINX(TempTable,[diff])
VAR MaxDiff = MAXX(TempTable,[diff])
RETURN
IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days")
Or is another result what you want?
Result 2 =
VAR TempTable =
CALCULATETABLE(
ADDCOLUMNS('Table',"diff",DATEDIFF('Table'[created date],'Table'[closed date],DAY)),
ALLEXCEPT('Table','Table'[BU],'Table'[Sub_bu])
)
VAR MinDiff = MINX(TempTable,[diff])
VAR MaxDiff = MAXX(TempTable,[diff])
VAR Result = IF(MinDiff<>MaxDiff,MinDiff&" - "&MaxDiff&" days",MinDiff&" days")
RETURN
IF(
ROWNUMBER(
ALL('Table'[BU],'Table'[Sub_bu],'Table'[created date],'Table'[closed date]),
ORDERBY('Table'[created date],ASC,'Table'[closed date],ASC),
PARTITIONBY('Table'[BU],'Table'[Sub_bu])
)=1,
Result
)
Demo - Merge the values into one.pbix
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~