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