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

## dax

i need to convert follwing calculated field WINDOW_SUM((IF [Minimun date 0]>=min(DATEADD('month',-12,[Date]) ) THEN [TR Count] ELSE 0 END),-11,0)/12 as PBI dax code

11 REPLIES 11
Frequent Visitor

can any one convert below tableau ( calulated field) as dax query

WINDOW_SUM((IF [LOOKUP(MIN(([Date])),0)]>=min(DATEADD('month',-12,[Date]) ) THEN [COUNTD([TRID])] ELSE 0 END),-11,0)/12

Frequent Visitor

Result as per tableau

result as per pbi ( which is providing incrrect result)

Community Support

You can try to use the following measure formula if helps:

``````formula =
VAR currDate =
MIN ( 'Table1'[Date] )
VAR result =
CALCULATE (
COUNTROWS ( VALUES ( 'Table1'[TRID] ) ),
FILTER (
ALLSELECTED ( 'Table1' ),
'Table1'[Date]
>= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) )
&& 'Table1'[Date] <= currDate
)
)
RETURN
result / 12``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Responsive Resident

Try this

PBI DAX Code =
CALCULATE(
AVERAGEX(
FILTER(
ALL('YourTable'),
'YourTable'[Minimun date 0] >= MINX(ALL('YourTable'), DATEADD('YourTable'[Date], -12, MONTH))
),
'YourTable'[TR Count]
),
'YourTable'[Date]
) / 12

Replace 'YourTable' with the actual name of your table.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Frequent Visitor

Hope for COUNTD([TRID]) we can use dax as COUNTROWS(VALUES('YourTableName'[TRID])) could you pls confirm dax for LOOKUP(MIN(([Date])),0)

Responsive Resident

If you want to get the minimum date from a column named [Date] where the lookup value is 0, you can use the following DAX expression:

LOOKUPVALUE('YourTableName'[Date], 'YourTableName'[YourLookupColumn], 0)
Replace 'YourTableName' with the actual name of your table, and 'YourLookupColumn' with the actual name of the column where you want to find the lookup value of 0.

This DAX expression uses the LOOKUPVALUE function to retrieve the value from the [Date] column where the value in the [YourLookupColumn] is equal to 0.

If you are looking for the minimum date across all rows where the lookup value is 0, you can use the following expression:

CALCULATE(MIN('YourTableName'[Date]), 'YourTableName'[YourLookupColumn] = 0)
Again, replace 'YourTableName' and 'YourLookupColumn' with the actual names of your table and lookup column.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Frequent Visitor

Its not working can you recheck it once again my code WINDOW_SUM((IF (LOOKUP(MIN(([Date])),0)>=min(DATEADD('month',-12,[Date]) ) THEN COUNTD([TRID]) ELSE 0 END),-11,0)/12

Responsive Resident

Is there an error you are getting

Frequent Visitor

its not showing any value

Responsive Resident

Try this

PBI DAX Code =
CALCULATE(
AVERAGEX(
FILTER(
ALL('YourTable'),
'YourTable'[Minimun date 0] >= MINX(ALL('YourTable'), DATEADD('YourTable'[Date], -12, MONTH))
),
'YourTable'[TR Count]
),
'YourTable'[Date]
) / 12

If this does not work can you share the sample file, and remove sensitive data?

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Frequent Visitor

YourTable'[Minimun date 0]  ITS NOT A Column its a calculated field LOOKUP(MIN(([Date])),0)

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.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors