cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gazzo1967
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
ValtteriN
Super User
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!






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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
gazzo1967
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 🙂

https://www.youtube.com/watch?v=z8hgSFQOPSM

 

ValtteriN
Super User
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!






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

Proud to be a Super User!




@ValtteriN 
thank you for replying.

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

 

 

 

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

gazzo1967_0-1642773685081.png

 

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.





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

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!