cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem Post Patron
Post Patron

Calculating a value based on current and previous month

I have a measure which counts how many passes and fails there are in a dataset called 'Loaders&Assessors Results' in a column called [Result]

The audit date is in a table called 'Loaders&Assessors' and is called [Date Audited]

 

The fail measure in its current form is

Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"))

I would like to add two additional measures, for current and previous month. I tried myself and failed. 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Calculating a value based on current and previous month

Hi @heytherejem 

 

I have tested this one in one of my datasets and it worked, give it a try:

 

Previous Month Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"),DATESBETWEEN('Loaders&Assessors'[Date Audited],DATE(YEAR(TODAY()),MONTH(TODAY())-1,01),DATE(YEAR(TODAY()),MONTH(TODAY()),01)-1))

Hope it helps!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Re: Calculating a value based on current and previous month

First make sure you have a relationship established between 'Loaders&Assessors' and 'Loaders&Assessors Results',  then try this:

 

Previous Month Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"), PREVIOUSMONTH('Loaders&Assessors'[Date Audited]))

Current Month Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"), DATESBETWEEN('Loaders&Assessors'[Date Audited],DATE(YEAR(TODAY()),MONTH(TODAY()),1),ENDOFMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))

 

heytherejem Post Patron
Post Patron

Re: Calculating a value based on current and previous month

Hi and thanks @Anonymous 

 

The measures don't quite work, I had tried the exact same previousmonth measure as you, and it returns no value although I can't see what the issue is!

 

The current month measure errored, I have played around with it and it seems the below is pulling through the correct number:

Current Month Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"), DATESBETWEEN('Loaders&Assessors'[Date Audited],DATE(YEAR(TODAY()),MONTH(TODAY()),1),ENDOFMONTH('Loaders&Assessors'[Date Audited])))

If you can think of a reason why the previousmonth measure it's pulling through a number, let me know!

Anonymous
Not applicable

Re: Calculating a value based on current and previous month

Hi @heytherejem 

 

I have tested this one in one of my datasets and it worked, give it a try:

 

Previous Month Fail = CALCULATE(COUNTA('Loaders&Assessors Results'[Result]),FILTER('Loaders&Assessors Results','Loaders&Assessors Results'[Result]="Fail"),DATESBETWEEN('Loaders&Assessors'[Date Audited],DATE(YEAR(TODAY()),MONTH(TODAY())-1,01),DATE(YEAR(TODAY()),MONTH(TODAY()),01)-1))

Hope it helps!

View solution in original post

heytherejem Post Patron
Post Patron

Re: Calculating a value based on current and previous month

It works! Hurrah! Thanks so much @Anonymous 

Anonymous
Not applicable

Re: Calculating a value based on current and previous month

Happy to help 🙂

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors