cancel
Showing results for
Did you mean:
Frequent Visitor

## Counting between todays Date and today -730 day

Hi all
Hope i find you well 🙂

I have a problem that i cant solve and asking for help.
I need to return a sum count of how many times the word 'Passed' in my [outcome] column shows in between todays date and todays date -730.
It probably is a very simple solution but i can't seem to get it 😞
thank you in advance for any help recieved 🙂

Gary

1 ACCEPTED SOLUTION
Super User

Hi,

Here is an example of the logic to use:

Measure 12 =
var _edate = calculate(max('Calendar'[Date]),'Calendar example'[Date]=TODAY(),ALL('Calendar'))
var _sdate =calculate(max('Calendar'[Date]),'Calendar example'[Date]=TODAY()-730,ALL('Calendar'))
return

CALCULATE(COUNT('Time'[Column1]),DATESBETWEEN('Calendar example'[Date],_sdate,_edate),ALL('Calendar'),'Time'[Column1]="Passed")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

5 REPLIES 5
Frequent Visitor

@ValtteriN
Thank you.
I found a youtube video explaining using a calendar model.
Once i did this it worked.
I have marked this as the solution
now i have to find how to return just the latest records from the table as there are duplicates in there 😞
For anyone who wants know how to create a calendar model this video explains it perfectly 🙂

Super User

Hi,

Here is an example of the logic to use:

Measure 12 =
var _edate = calculate(max('Calendar'[Date]),'Calendar example'[Date]=TODAY(),ALL('Calendar'))
var _sdate =calculate(max('Calendar'[Date]),'Calendar example'[Date]=TODAY()-730,ALL('Calendar'))
return

CALCULATE(COUNT('Time'[Column1]),DATESBETWEEN('Calendar example'[Date],_sdate,_edate),ALL('Calendar'),'Time'[Column1]="Passed")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Proud to be a Super User!

Frequent Visitor

@ValtteriN

Im new to this and im picking up somebody elses work 😞 (i normal work with Powerapps and Automate)
No doubt i have it completely wrong as it doesnt work when i place my column names in there 😞

My columns are
Table = 'BA1 Hot wear'

date Column = 'BA1 Hot wear'[Date of Assessment]
Text column =  'BA1 Hot wear'[Assessment Outcome]

So i have this but with questions.

Measure 12 =
var _edate = calculate(max('BA1 Hot wear'[Date of Assessment]),'Calendar example'[Date]=TODAY(),ALL('BA1 Hot wear'))
var _sdate =calculate(max('BA1 Hot wear'[Date of Assessment]),'Calendar example'[Date]=TODAY()-730,ALL('BA1 Hot wear'))
return

CALCULATE(COUNT('BA1 Hot wear'[Assessment Outcome]),DATESBETWEEN('Calendar example'[Date],_sdate,_edate),ALL('BA1 Hot wear'),'BA1 Hot wear'[Assessment Outcome]="Passed")

Is the 'Calendar example'[Date] = 'BA1 Hot wear'[Date of Assessment]

Thanks
Gary

Frequent Visitor

I made the assumtion on my last message that it was the column as asked but i get this message.

Super User

Hi,

It is generally a good idea to have a calendar table in your data model. The column I am referring to is an example of this.

Proud to be a Super User!

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!