Forum Discussion

JCK2's avatar
JCK2
Helper 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
Community 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.

  • JCK2's avatar
    JCK2
    Helper III
    6 years ago

    Maggie, this is great, which means i can possible use that new data as a slicer for the users. 

     

    For count of completed, instead of compliance date, i should be using date performed.

     

    I will let you know, how it is going to work out..

    • JCK2's avatar
      JCK2
      Helper III
      6 years ago

      Maggie, can i use this new table as a master date slicer; means connecting multiple date to this date table. I have other things that is shown in the dashboard. ( No# compliance, No# Actions etc..). So can i join the initiation date of all (Compliance, Action) and create a join with this new year table? and use them as a master date slicer on the dashboard...

       

      Thanks a lot!