Forum Discussion

MillarJayakumar's avatar
MillarJayakumar
Frequent Visitor
2 years ago

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

Tips and Tricks

11 Replies

  • saud968's avatar
    saud968
    Memorable Member
    2 years ago

    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's avatar
      MillarJayakumar
      Frequent Visitor
      2 years ago

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

      • saud968's avatar
        saud968
        Memorable Member
        2 years ago

        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's avatar
    MillarJayakumar
    Frequent Visitor
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

    • MillarJayakumar's avatar
      MillarJayakumar
      Frequent Visitor
      2 years ago

      Result as per tableau 

      result as per pbi ( which is providing incrrect result)