Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have the following measures with the idea to calculate deviation between months:
@carnage66 , Try Datesmtd with date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
or
MTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESMTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALMTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LMTD QTY forced=
var _max = date(year(today()),month(today())-1,day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
Thanks, I will use these for previous month but unfortunately none of them help with the idea of a projection for the current month. The main idea is to create a measure where if the month is not complete, divides the sum by current date and multiplies it by the max days in the month, while for complete months shows just the sum.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
43 | |
26 | |
21 |