Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
Hi Experts
how can i change the following measure to give me previous month
Solved! Go to Solution.
Hi @Anonymous ,
I created some data:
Here are the steps you can follow:
1. Create measure.
P1 Emergency KPI =
VAR ClosedP1Jobs =
CALCULATE (
COUNT ( 'All Data'[WO No] ),
FILTER (
ALL ( 'All Data' ),
AND (
'All Data'[Priority] = "e",
'All Data'[Report Date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
&& 'All Data'[Report Date]
<= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), 0 )
&& 'All Data'[Source Table] = "completed"
)
)
)
VAR p1JobsMTD =
CALCULATE (
COUNT ( 'All Data'[WO No] ),
FILTER (
'All Data',
OR (
'All Data'[Source Table] = "Reactive",
'All Data'[Source Table] = "Completed"
)
&& AND (
'All Data'[Report Date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
'All Data'[Report Date]
<= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), 0 )
&& 'All Data'[Priority] = "e"
)
)
)
RETURN
DIVIDE ( ClosedP1Jobs, p1JobsMTD )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous ,
I created some data:
Here are the steps you can follow:
1. Create measure.
P1 Emergency KPI =
VAR ClosedP1Jobs =
CALCULATE (
COUNT ( 'All Data'[WO No] ),
FILTER (
ALL ( 'All Data' ),
AND (
'All Data'[Priority] = "e",
'All Data'[Report Date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
&& 'All Data'[Report Date]
<= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), 0 )
&& 'All Data'[Source Table] = "completed"
)
)
)
VAR p1JobsMTD =
CALCULATE (
COUNT ( 'All Data'[WO No] ),
FILTER (
'All Data',
OR (
'All Data'[Source Table] = "Reactive",
'All Data'[Source Table] = "Completed"
)
&& AND (
'All Data'[Report Date]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
'All Data'[Report Date]
<= EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), 0 )
&& 'All Data'[Priority] = "e"
)
)
)
RETURN
DIVIDE ( ClosedP1Jobs, p1JobsMTD )
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@Anonymous , if you need based on today
MTD Today =
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
LMTD Today=
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
LYMTD Today =
var _min = eomonth(today(),-13)+1
var _max1 = today()-1
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
MTD Yesterday =
var _max = today() -1
var _min = eomonth(_max,-1)+1
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
Same Month Last Year Today =
var _min = eomonth(today(),-13)+1
var _max = eomonth(today(),-12)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
If you need based on selected date use time intelligence
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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Or try offset -
Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
User | Count |
---|---|
88 | |
74 | |
69 | |
59 | |
56 |
User | Count |
---|---|
40 | |
38 | |
34 | |
32 | |
28 |