Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi Team,
I have scenario as follows.
I want to carry saved hrs till Dec however starting point will be month on metioned date.
Need output in vertical format.
The orange highlited (Date and SavedHrs) columns are table. Horizontal Jan to Dec to get logic of calculation.
and vertical table is actual output that we require.
You should do this in Power Query with unpivot or pivot function (always forget which is which).
Consider giving it thumbs up and accept as solution if this helps!
@NILU090 , Join date with date of date table and create measures like
ytd =calculate( sum(Table[Save Hours]), datesytd(Date[Date]))
Ytd Saved = sumx(Values(Date[Month Year]), [ytd])
Calendar =
var _tab = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Qtr Start Date", var _rem = mod(month([Date]),3)
return eomonth([Date], Switch(_rem,2,-1, 0,-2, 1,-3))+1
)
return
Addcolumns(_tab,
"Qtr Rank", rankx(_tab,[Qtr Start Date], ,asc,dense),
"Qtr Day", datediff([Date],[Qtr Start Date], day)+1
)
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |