Forum Discussion
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...
- 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)
)
)
AllisonKennedy
Community Champion
Community ChampionYou 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.
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.
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.
- AllisonKennedyThe 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)
)
)Thank you very much.