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
MC007
New Member

Count Tickets Created Last Year and This Year Over Same Date

Hi all,

 

I am trying to calculate the number of Tickets last year for the same tinme period as this year.

TCTD 2020 should count 01/01/20 and Today - 1 Year 

 

Below is what I have written but i am not getting the count i'm expecting.

TCTD 2020 = CALCULATE(count('Incidents'[Number])+count('Change Request'[Number])+count(Problems[Number]),DATESBETWEEN('Date Created'[Date],DATE( 2020, 01, 01),TODAY()-365))
 
Any help is appreciated.
Thanks
2 ACCEPTED SOLUTIONS
V-lianl-msft
Community Support
Community Support

Try to create measures like this:

this_year = TOTALYTD(COUNT('Change Request'[Number])+COUNT(Incidents[Number])+COUNT(Problems[number]),'Table'[Date])
last_year = CALCULATE([this_year],SAMEPERIODLASTYEAR('Table'[Date]))

View solution in original post

MC007
New Member

Hi all,
Thank you very much for all your help.
I used the below which works for me:
CALCULATE(count('Incidents'[Number]), 'Date Created'[Year]=2020)/DATEDIFF("01/01/2020","31/12/2020",DAY)
CALCULATE(count('Incidents'[Number]), 'Date Created'[Year]=2021)/DATEDIFF("01/01/2021",NOW(),DAY)

View solution in original post

3 REPLIES 3
MC007
New Member

Hi all,
Thank you very much for all your help.
I used the below which works for me:
CALCULATE(count('Incidents'[Number]), 'Date Created'[Year]=2020)/DATEDIFF("01/01/2020","31/12/2020",DAY)
CALCULATE(count('Incidents'[Number]), 'Date Created'[Year]=2021)/DATEDIFF("01/01/2021",NOW(),DAY)
V-lianl-msft
Community Support
Community Support

Try to create measures like this:

this_year = TOTALYTD(COUNT('Change Request'[Number])+COUNT(Incidents[Number])+COUNT(Problems[number]),'Table'[Date])
last_year = CALCULATE([this_year],SAMEPERIODLASTYEAR('Table'[Date]))
amitchandak
Super User
Super User

@MC007 , with help from the date table you get a year behind the measure

 

Year behind Sales = CALCULATE(count('Incidents'[Number]),dateadd('Date'[Date],-1,Year))

or
Year behind Sales = CALCULATE(count('Incidents'[Number]),SAMEPERIODLASTYEAR('Date'[Date]))

 

or

 

1 day 1 year behind today

LYTD =
var _min = date(year(today())-1,1,1)
return
CALCULATE(count('Incidents'[Number]), FILTER(ALL('Date'),'Date'[Date] = _min))

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.