Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there guys,
I have a simple date column in my dataset and I need to calculate sum of the revenue as MTD, but the week in our terms start on Friday and ends on Thursday.Thus, if today is 25th of Jan, then MTD would only calculate until 21th of Jan included. Any ideas how can I do that?
I cant figure it out for the previous month, @amitchandak have you got any ideas?
@Anonymous , Please create a Friday Thrusday week using my blog and then have formula like
Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
MTD QTY forced=
var _max = maxx(allselected(Date), Date[week Start date])
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))
@amitchandak Dude, thank you so much, that works. Now I need to add one more column but for the same period last month, how should I adjust the formula? Its literally the same calculation, just for the previous month.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |