Forum Discussion
Absence Data Plotted as Lost Days Per Month
Hi Anonymous
The values for each month column are calculated in Excel with the below formula:
=IF(AND($B2<(H$1),$C2<(H$1)),0,IF($B2>EOMONTH(H$1,0),0,IF(AND($B2>=(H$1),$C2<=EOMONTH(H$1,0)),(NETWORKDAYS($B2,$C2)/5)*$D2,IF(AND($B2>=H$1,$C2>EOMONTH(H$1,0)),(NETWORKDAYS($B2,EOMONTH(H$1,0))/5)*$D2,IF(AND($B2<=H$1,$C2>=EOMONTH(H$1,0)),(NETWORKDAYS(H$1,EOMONTH(H$1,0))/5)*$D2,IF(AND($B2<=H$1,$C2<=EOMONTH(H$1,0)),(NETWORKDAYS(H$1,$C2)/5)*$D2,0))))))
B2 = Abs_Start_Date
H1 = Month Column Header (01/01/2016 etc. dispalyed as MMM/YY)
C2 = Abs_End_Date
D2 = Con_Work_Days
Hopfully that helps.
Thanks, G.
Hi Anonymous,
Your formula is complex and I try to format it as dax measure formula. You can try it if it works on matrix visual.(it think it should add more conditions to handle total level calculation)
Measure =
VAR currStart =
MIN ( [Abs_Start_Date] )
VAR currEnd =
MAX ( [Abs_End_Date] )
VAR currWorkday =
MAX ( [Con_Work_Days] )
VAR selected =
MAX ( Expand[Detail Date] )
VAR workdays =
COUNTROWS (
FILTER ( CALENDAR ( currStart, currEnd ), WEEKDAY ( [Date], 2 ) <= 5 )
)
VAR _lastdate =
DATE ( YEAR ( selected ), MONTH ( selected ) + 1, 1 )
- 1
RETURN
IF (
currStart < selected
&& currEnd < selected,
0,
IF (
currStart > _lastdate,
0,
IF (
currStart >= selected
&& currEnd <= _lastdate,
workdays / 5
* currWorkday,
IF (
currStart >= selected
&& currEnd > _lastdate,
COUNTROWS (
FILTER ( CALENDAR ( currStart, _lastdate ), WEEKDAY ( [Date], 2 ) <= 5 )
)
/ 5
* currWorkday,
IF (
currStart <= selected
&& currEnd >= _lastdate,
COUNTROWS (
FILTER ( CALENDAR ( selected, _lastdate ), WEEKDAY ( [Date], 2 ) <= 5 )
)
/ 5
* currWorkday,
IF (
currStart <= selected
&& currEnd <= _lastdate,
COUNTROWS (
FILTER ( CALENDAR ( selected, currEnd ), WEEKDAY ( [Date], 2 ) <= 5 )
)
/ 5
* currWorkday,
0
)
)
)
)
)
)
Regards,
Xiaoxin Sheng