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
Anonymous
Not applicable

Compare tickets this month with previous month

Hi, wondering if someone could help me with this. I have a measure called [Total] that shows a count of how many tickets were created. What I want to do is show the current number of tickets created this month, compared to the same time last month.

Example, if I am looking at my dashboard today it will show 465 tickets that were created from 1 Feb 2020 to 27 Feb 2020. The previous month measure will calculate the [Total] measure for the period of 1 Jan 2020 to 27 Jan 2020. How can I get this to work? I have tried PARALLELPERIOD but this is calculating for the entire of January?

 

ParallelPeriod:=CALCULATE([Total],PARALLELPERIOD('Calendar'[Date],-1,MONTH))

 

Appreciate any guidance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The below worked 🙂

Test 2 month behind = CALCULATE([Total Phone Work Per FTE],DATESMTD(ENDOFMONTH(DATEADD('Date'[date],-1,MONTH))))

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

The below worked 🙂

Test 2 month behind = CALCULATE([Total Phone Work Per FTE],DATESMTD(ENDOFMONTH(DATEADD('Date'[date],-1,MONTH))))
Anonymous
Not applicable
Anonymous
Not applicable

These just seem to be calculating the measure for the entire month of January and not up to the 27th which is what it should be doing?

In order for me to do an accurate comparrison to the previous month it needs to also take into account the days as well. So if I viewed the report on 3rd of March, it would show a comparrison of tickets created between '01 Mar 2020' and '3 mar 2020'  vs '1 feb 2020, and '3 feb 2020'

Anonymous
Not applicable

Sales for current month = CALCULATE (SUM (Sales[TotalProduct]), FILTER (ALL('Date Table'), 'Date Table'[MonthDate]<= Today() && 'Date Table'[MonthDate]>= DATE (YEAR ( TODAY ()), MONTH (TODAY () ) , 1 )))

 

Sales for Previous month = CALCULATE (SUM (Sales[TotalProduct]), FILTER (ALL('Date Table'), 'Date Table'[MonthDate]<= Dateadd(Today(),-1,Month) && 'Date Table'[MonthDate]>= DATE (YEAR ( TODAY ()), MONTH (TODAY () )-1 , 1 )))

 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos...

Any time intelligence function needs an end date. That date is either selected using slicer or end date of the calendar .

So first two formula is based on that. The third one I tried forcing date for last month

Datesmtd and totalmtd are for month till date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),
Date[Date]<date(year(today()),month(today())-1,day(today())))

last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

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.