cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need dax solution which will create table and caulculations

Hi Team,

I have following input data.

need following output.
Basically when we talk about Rating 2, it should pick max date of Rating 2 and minus min date of Rating 1 for Tkt1 ID.

Same with Rating 3, it should pick maximum date of rating 3 and minus minimum date of rating 2.

all this i want to achive thorugh DAX. No extra table. Thanks in advance

1 ACCEPTED SOLUTION
Community Support

Hi  @Narendra90 ,

Here are the steps you can follow：

1. Create calculated column.

``````Output1 =
var _date1=
MAXX(    FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])),[New date])
var _date2=
MINX(    FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])-1),[New date])
var _value=
DATEDIFF(
_date2,_date1,DAY)
var _count=
COUNTX(  FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])),[Rating])
return
IF(
'Table'[Rating]=
MINX(
ALL('Table'),[Rating]),0,
_value / _count)``````

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Community Support

Hi  @Narendra90 ,

Here are the steps you can follow：

1. Create calculated column.

``````Output1 =
var _date1=
MAXX(    FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])),[New date])
var _date2=
MINX(    FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])-1),[New date])
var _value=
DATEDIFF(
_date2,_date1,DAY)
var _count=
COUNTX(  FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Rating]=EARLIER('Table'[Rating])),[Rating])
return
IF(
'Table'[Rating]=
MINX(
ALL('Table'),[Rating]),0,
_value / _count)``````

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors