Forum Discussion

JCK2's avatar
JCK2
Icon for Helper III rankHelper III
6 years ago
Solved

Dax column calculation

Hello,   i want to create a measure to arrive at one number as how much is due in the month, I wrote teh below calculation in column, however this is not helping me achive what i want.   How does...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi JCK2 

My solution below is to calculate %completed based on "Compliance date" every month.

Create a date table

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Then create a column

over due =
IF (
    (
        [Compliance Date] < [Date Audit Performed]
            || ISBLANK ( [Date Audit Performed] )
    )
        && (
            [Compliance Date]
                <> BLANK ()
                && [Compliance Date]
                    < TODAY ()
        ),
    1,
    0
)

create measures

count of completed =
CALCULATE (
    COUNT ( 'Table'[Compliance Date] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[over due] = 1
    ),
    USERELATIONSHIP ( 'date'[Date], 'Table'[Compliance Date] )
)

countall = CALCULATE(COUNT('Table'[Compliance Date]),USERELATIONSHIP('date'[Date],'Table'[Compliance Date]))

% = [count of completed]/[countall]

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.