cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Dax column calculation

@JCK2  - Yep!

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

20 REPLIES 20
Highlighted
Super User IV
Super User IV

Re: Dax column calculation

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Second, maybe:

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Dax column calculation

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

Capture12.JPG

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]

Capture14.JPG

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.

Highlighted
Helper II
Helper II

Re: Dax column calculation

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

Highlighted
Helper II
Helper II

Re: Dax column calculation

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

Highlighted
Helper II
Helper II

Re: Dax column calculation

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.

 

SAM Audit.JPG

 

 

Highlighted
Helper II
Helper II

Re: Dax column calculation

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!

Highlighted
Helper II
Helper II

Re: Dax column calculation

@Greg_Deckler  I have now been able to create 2 columns

a) Audits Due

b) Audits Over due

 

Now i just want to create a calculation by creating a column

 

Audit Due-Overdue/Audits due to get the % completed on time.

 

However I am getting a circular dependcy error 😞SAM Audit.JPG

Highlighted
Super User IV
Super User IV

Re: Dax column calculation

@JCK2 OK, trying to come back up to speed on this, what are your formulas for the three columns?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Dax column calculation

@Greg_Deckler 

 

These are the calculations i am using...

Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))

 

 

Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed])||  ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)

 

Thanks for checking Greg!!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors