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
hongyuliu
Helper III
Helper III

distributing duration across month.

I need a matrix to calculate the number of working days per person & per month. I've used the method in this video,https://www.youtube.com/watch?v=R8tDKwimSsY.

But the result is not what I'm looking for, it doesn't display all people. Below is the original data and matrix.

4.PNG2.PNG

I guess the problem should be I have many rows for one person, and sample only has one row for each ID. I tried again after I remove all rows except the first row, the result is what I want.

1.PNG

Below is the DAX function I copied from sample. Please advise how to modify it.

3.PNG

 

Thank you very much.

1 ACCEPTED SOLUTION

The SELECTEDVALUE is not needed and will mess up the formula if you leave it in, so try:

Dist =

VAR _MinDate = FIRSTDATE(Dates[Date])
VAR _MaxDate = LASTDATE(Dates[Date])

RETURN
SUMX('data 1',
VAR _Start = 'data 1'[Date From]
VAR _End = 'data 1'[Date To]

RETURN
IF(
(_End<=_MaxDate && _End>=_MinDate)
|| (_Start>=_MinDate && _Start<=_MaxDate)
,

DATEDIFF(MAX(_Start, _MinDate), MIN(_End, _MaxDate),DAY)
)
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

You might be able to simplify that measure a bit to remove the IF statement and just subtract MAX(_start, mindate) or something similar.

To solve your problem with multiple IDs, this is due to the SELECTEDVALUE part of the formula, which only returns a value when there is 1 selected value, so for A1 with 4 start dates, DAX cannot find a selected value and therefore returns the alternate result, which you have left out in your DAX, so Blank().

You can nest your entire formula inside a SUMX, including the variables:
SUMX('data 1', insert your measure formula here)
and that will return the total duration for each line of your data table and then add them all up. Putting the formula inside the SUMX('data 1' enables you to iterate over each row of the data 1 table, and therefore when you define the variables for SELECTEDVALUE you only have 1 date for each row and don't get blank.

I'm not sure if that's the result you want as you have some overlapping days for some of those IDs, so you may want a MAXX or AVERAGEX instead, but something to iterate will help you out.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison,

 

Now I understand what the problem is. I tried to add a SUMX(), but it doesn't work.

Can you please try with following data? I want to see how you will write the formula. Thank you.

1.PNG

2.PNG

The SELECTEDVALUE is not needed and will mess up the formula if you leave it in, so try:

Dist =

VAR _MinDate = FIRSTDATE(Dates[Date])
VAR _MaxDate = LASTDATE(Dates[Date])

RETURN
SUMX('data 1',
VAR _Start = 'data 1'[Date From]
VAR _End = 'data 1'[Date To]

RETURN
IF(
(_End<=_MaxDate && _End>=_MinDate)
|| (_Start>=_MinDate && _Start<=_MaxDate)
,

DATEDIFF(MAX(_Start, _MinDate), MIN(_End, _MaxDate),DAY)
)
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thank you very much.

Fowmy
Super User
Super User

@hongyuliu 

Can you share some sample data which I can copy-paste in Excel or your PBIX file along with desired results? Also, on what fields have you connected the calendar table to the data1 table?


________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi Fowmy,

Got some problem to insert table, sorry. Please kindly enter the data in your excel and try. I didn't connect any column between Calendar and Data1. If you want, it's fine. Thank you very much.

Data:

1.PNG

Result:

2.PNG

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

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.