Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
The below worked 🙂
Test 2 month behind = CALCULATE([Total Phone Work Per FTE],DATESMTD(ENDOFMONTH(DATEADD('Date'[date],-1,MONTH))))
The below worked 🙂
Test 2 month behind = CALCULATE([Total Phone Work Per FTE],DATESMTD(ENDOFMONTH(DATEADD('Date'[date],-1,MONTH))))
Try this
https://community.powerbi.com/t5/Desktop/Compare-Current-vs-Previous-Month-with-DAX/m-p/926068
or
https://community.powerbi.com/t5/Desktop/Compare-Value-Against-Previous-Month-Value/m-p/925523
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
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'
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/
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |