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

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.

Reply
AppleMan
Helper II
Helper II

Help getting a YTD function to work as intended

Hi,

 

I am working on a report that takes employees entered hours and then calculates the number of active salary employees and their hours worked in a given month. These hours are then added together to get a total hours variable which is then used to calculate the YTD total of these hours. 

 

The issue is that when this YTD function runs it is using the current months active employee count with the previous months day count and gets the incorrect total for YTD. This can be seen in the attached example since the total hours column is correct, but the YTD side of it gives the wrong total. For example, January + February's total hours combined should be 112,799.04, however it is calculating as 112,976.18. This is because it is using 42 as the employee count for January in the YTD portion, instead of using the correct 41. 

 

The other hurtle with this report is I have wrote the report in a way that future months will not display any salary hours (since there are no hourly employees hours yet either), however in the YTD column it then takes the total of hourly workers hours and excludes any salary hours and displays that. What I wanted it to do is show the current months YTD total for all future months. 

 

I have tried to troubleshoot this on my own and have not been able to find a good solution or the correct way to write the measures to handle this. Any help would be greatly appreciated, let me know if I can clear up any specifics!

 

The example below shows the current results with a table showing what I am trying to have it display instead.

Link to the example: Example 

1 ACCEPTED SOLUTION
rajulshah
Super User
Super User

@AppleMan 

Please find the updated file here - link

 

This is the output you wanted. Please confirm.

rajulshah_0-1684566407075.png

Please let me know if this didn't work.

View solution in original post

2 REPLIES 2
rajulshah
Super User
Super User

@AppleMan 

Please find the updated file here - link

 

This is the output you wanted. Please confirm.

rajulshah_0-1684566407075.png

Please let me know if this didn't work.

Works absolutely perfect, thank you so much! 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.