dax

[MillarJayakumar]

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

[MillarJayakumar]

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

[MillarJayakumar]

Result as per tableau 

result as per pbi ( which is providing incrrect result)

[v-shex-msft]

Hi @MillarJayakumar,

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

[saud968]

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]
),
DATEADD('YourTable'[Date], -11, MONTH),
'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!

[MillarJayakumar]

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

[saud968]

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!

[MillarJayakumar]

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

[saud968]

Is there an error you are getting

[MillarJayakumar]

its not showing any value

[saud968]

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]
),
DATEADD('YourTable'[Date], -11, MONTH),
'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!

[MillarJayakumar]

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