Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Manuel123
Helper I
Helper I

Year to Date on hourly values

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

2 REPLIES 2
amitchandak
Super User
Super User

@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]))

 

Dear @amitchandak,

 

thank you very much. What do you mean by 'Date'[Year Rank]?

 

Best regards

Manuel

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.