cancel
Showing results for
Did you mean:
Highlighted
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

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

Proud to be a Super User!

20 REPLIES 20
Highlighted
Super User IV

## Re: Dax column calculation

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!

Proud to be a Super User!

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

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.

Highlighted
Helper II

## Re: Dax column calculation

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

Highlighted
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

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

Highlighted
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

## 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 😞

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

Proud to be a Super User!

Highlighted
Helper II

## Re: Dax column calculation

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

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

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

Top Solution Authors
Top Kudoed Authors