distributing duration across month.

[hongyuliu]

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.

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.

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

Thank you very much.

[AllisonKennedy]

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.

[hongyuliu]

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.

[AllisonKennedy · 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)
)
)

[hongyuliu]

Thank you very much.

[Fowmy]

@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

[hongyuliu]

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:

Result:

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.