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

[APS_SW]

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:

Date	Hours Worked (SUM)	Hours Worked Last Year (SUM)
01/01/2024	0	5
02/01/2024	0	1903.56
03/01/2024	2045.78	1847.64
04/01/2024	2309.67	1945.04
05/01/2024	1239.53	1643.75
06/01/2024	1903.56	1948.24
07/01/2024	1936.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?

[APS_SW · Accepted solution]

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]

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.

[arava]

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. 

[APS_SW]

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.