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 this work in a measure?

 

Over due in the month = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed])||  ISBLANK(' Audits'[Date Audit Performed]) &&'SAM Supplier Audits'[Compliance Date]< TODAY(),1,0)

 

Also want to add an additional condition of Compliance date is No Null...

 

I want to further calculate % Completed on time...using this as one of input..

Thanks a lot!! 

 

Need Help

20 Replies

  • 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!

    • JCK2's avatar
      JCK2
      Helper III
      6 years ago

      Thanks a lot Greg!! I am going to try this.

      • JCK2's avatar
        JCK2
        Helper III
        6 years ago

        Greg, i used the calculation and when i dragged it into a table it works, but when i want to get a total, it does not seem to work

         

        For eg: by month or by year, like an aggregated count, it does not seem to work. I think its because we are using the max calculation and what it does is calculate the over due on the latest date avaialable and showing the result as zero and not showing a count of all over dues.