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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
APS_SW
Regular Visitor

Calculate Last Years Hours by Day but multiple entries per daY.



OK, the following is a stripped down of the table I am working with:

 

Table Name: timeTracked
Fields:

dateTimeTracked (DATETIME)
hoursWorked (DECIMAL)

 


What I am attempted to do is add another field called "hoursWorkedLastYear"

So I can build a report with say:

DateHours Worked (SUM)Hours Worked Last Year (SUM)
01/01/202405
02/01/202401903.56
03/01/20242045.781847.64
04/01/20242309.671945.04
05/01/20241239.531643.75
06/01/20241903.561948.24
07/01/20241936.23 67.5



I built somethgin similar using the following code and this worked just fine.

 

LastYearsHours_OLD = CALCULATE(
    SUM(Fact_Table[Hours]),
    SAMEPERIODLASTYEAR(Fact_Table[WorkHoursDate])
)

 


Unfortunatly the dateTimeTracked field has multiple values for each day.

I built a calender table to link to the datetime field and this 'sort of works' but in order to view 2024 data you need to view the date filter to BOTH 2023 and 2024 and 2023 is blank but  2024 is there and filled out.

Any suggestions ideas of what I could try?

1 ACCEPTED SOLUTION
APS_SW
Regular Visitor

Figured it out.

The DATETIME field was being linked to the DATE table and thus only the hours recorded on 00:00:00 were being recorded.

Created a DATE field on the table and linked it up correctly works now with a simple: 

LastYearsHours_OLD = CALCULATE(
    SUM(Fact_Table[Hours]),
    ALL(),
    SAMEPERIODLASTYEAR(Fact_Table[WorkHoursDate])
)


Amazing what a bit of sleep gets you.

View solution in original post

4 REPLIES 4
APS_SW
Regular Visitor

Figured it out.

The DATETIME field was being linked to the DATE table and thus only the hours recorded on 00:00:00 were being recorded.

Created a DATE field on the table and linked it up correctly works now with a simple: 

LastYearsHours_OLD = CALCULATE(
    SUM(Fact_Table[Hours]),
    ALL(),
    SAMEPERIODLASTYEAR(Fact_Table[WorkHoursDate])
)


Amazing what a bit of sleep gets you.

Ashish_Mathur
Super User
Super User

Hi,

I cannot spot any mistake there especially after you mention that you have created a Calendar Table.  You should just need to select 1 year.  Ensure that year is dragged from the Calendar Table.

If it still does not help, then share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
arava
Regular Visitor

If possible, I would do a Group By in Power Query to roll up these hours, creating a composite key consisting of User ID and Date. 

 

What is currently happening is that Power BI cannot find last years info while it is filtered out, therefore it must be included. I would place this measure in a column to resolve. 

I do not have access to do the power query sadly. it all has to be done by calculated columns and measures.

I've been investigating the ALLEXCEPT() and ALL() and not managing to hit on the correct code. not even sure if it is possible like that but that is so far my only avenue which I have been bashing my head against a wall with for quite some time now.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.