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
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)