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
cia61
New Member

Working with Dates

I have to report which Labor periods the data captured falls within.  So instead of using just the calendar month to filter data, I have to use time periods that cross calendar months.   I can use a DAX equation like the one below but it seems like If I used a "Labor Dates table (see below) and create a relationship to the time entered data I could do this more efficiently.

 

Dax Equation

YTD Labor = if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,1,4) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,1,31) ,"January",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,2,1) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,2,28) ,"February",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,3,1) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,3,28) ,"March",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,3,29) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,5,2) ,"April",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,5,3) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,5,30) ,"May",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,5,31) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,6,27) ,"June",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,6,28) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,8,1) ,"July",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,8,2) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,8,29) ,"August",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,8,30) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,9,26) ,"September",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,9,27) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,10,31) ,"October",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,11,1) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2021,11,28) ,"November",
                if('Akron Cyient Submitted Timeshee'[Work Date].[Date] >= DATE(2021,11,29) && 'Akron Cyient Submitted Timeshee'[Work Date].[Date] <= DATE(2022,1,1) ,"December","NA"))))))))))))
 
Labor Table
 
cia61_0-1628256185742.png

 

2 REPLIES 2
amitchandak
Super User
Super User

@cia61 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

refer if this can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Although this points me in a direction I am having a hard time correlating this solution with the solution I need. 

 

How do I upload sample data?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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