Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Community,
I hope you can help me. I know how to get a running total and year-to-date based on daily values. However, it doesn't work for hourly values. Is there a "standard" way to get a year-to-date value for hourly values?
Looking forward to your feedback.
Best regards
Manuel
@Manuel123 , if you need till now
YTD Now=
var _min = date(year(today()),1,1)
var _day = Now()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Datetime] >=_min && 'Date'[Datetime] <= _day) )
If you need put hour too, then you need to have date and time table
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) )
, filter(all(Time), time[Hour] <= max(time[Hour])))
column you need
in date Table
Day of Year =datediff([Year Start date] , [Date],Day) +1
In fact to separate date and time
Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))
Time = [datetime].Time
or
Time = Time(hour([datetime]),minute([datetime]),second([datetime]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |