Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gazzo1967
Helper III
Helper III

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
Helper III
Helper III

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors