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
zhivaninz
Helper I
Helper I

DATESINPERIOD where measure refers to the date table - error, or working as intended?

We have a measure that calculates gross worked days that is:

Gross Worked Days =

Total Staff (Full Time Equivalents) * Working Days In Period (we only have monthly grain)

Working Days In Period is derived from the Calendar/Date dimension.

 

When we try to calculate a rolling number using DATESINPERIOD, we get erroneous results. When we use the exact same syntax but use measures that don't use the Calendar/Date dimension as a variable, we get correct results.

 

We've come up with workarounds (getting the Working Days In Period from a separate table), but interested in whether this is a bug or just DATESINPERIOD working as intended?

1 ACCEPTED SOLUTION

Hi @zhivaninz ,

Thanks for your reply. Please create the relationship between the above two tables base on the field [Data Date] and [Date] and check if that can help you get the expected result....

Table one is employees/FTE and is of this format:
Data Date Employee # FTE
1/12/2023 1 0.9



Table two is a calendar file and is of this format:

Date Month WorkDaysInMonth
1/12/2023 Dec-2023 19

vyiruanmsft_0-1705916900831.png

 

If the above one can't help you figure out, what's your expected result? It is better if you can share a simplified pbix file with expected result base on your provided data. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @zhivaninz ,

Thanks for reaching out to us with your problem. Base on your description, it seems that you got stucked when tried to get the number of worked days. In order to get a better understanding on your problem and give you a suitable solution, could you please provide some raw data in your tables (exclude sensitive data) with Text format, the formula of measure and your expected result with special examples and screenshots? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

 

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

Table one is employees/FTE and is of this format:

Data DateEmployee #FTE
1/12/202310.9



Table two is a calendar file and is of this format:

DateMonthWorkDaysInMonth
1/12/2023Dec-202319

 

Measure 1 is:

mFTE =SUM[FTE]

Measure 2 is:
mWorkedDays= mFTE*MAX(WorkDaysInMonth)

Measure 1 works for a 3-month rolling period. Measure 2 works for individual periods.
Measure 3 uses DATESINPERIOD. If we drop Measure 1 in here, it works. Measure 2 returns erroneous results.

Hi @zhivaninz ,

Thanks for your reply. Please create the relationship between the above two tables base on the field [Data Date] and [Date] and check if that can help you get the expected result....

Table one is employees/FTE and is of this format:
Data Date Employee # FTE
1/12/2023 1 0.9



Table two is a calendar file and is of this format:

Date Month WorkDaysInMonth
1/12/2023 Dec-2023 19

vyiruanmsft_0-1705916900831.png

 

If the above one can't help you figure out, what's your expected result? It is better if you can share a simplified pbix file with expected result base on your provided data. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.