DAX Help Please. Calculate the total days for multiple people on multiple tasks in a time period.

[MORGANC846]

Hi All,

I am hoping someone can help me. I am trying to create a measure to display on a card, that will calculate the total days spent on multiple tasks by multiple people within 'this' month.

So far I have the measure below, which is close, but totals the enitre duration of tasks that fit the criteria, i.e the start and end date fall within the parameters, however I only want it to include the days on task in 'this' month. Month being determined by a slicer / filter.

VAR WFDS This Month =

VAR MaxDate = MAX('CALENDAR'[Date])

VAR MinDate = MIN('CALENDAR'[Date])

RETURN

CALCULATE(

[duration](

FILTER(

'TableA' , 'TableA'[START DATE] <= MaxDate && 'TabelA'[END DATE] >= MinDate ) ) ,

CROSSFILTER(

'CALENDAR'[Date] , 'TabelA'[START DATE] , None ) )

This is a small sample of the data I am using.

For each task how many workforce days have been spent in 'this' month?

Anne for example has spent 109 workforce days on Task A, but how many were spent in October 22? She was committed for the whole of October so 31.  Additionally, Kelly for example has spent 35 days on Task C, however only 20 of those days were spent in October. The current Dax includes the full duration of both Anne and Kelly's tasks.

Ser	Event Type	Event Name	Start Date	End Date	Duration	Name
1	Task	A	29/08/2022	16/12/2022	109	Anne
2	Task	A	29/08/2022	16/12/2022	109	Brian
3	Task	A	29/08/2022	16/12/2022	109	Colin
4	Task	A	29/08/2022	16/12/2022	109	Dave
5	Task	A	29/08/2022	16/12/2022	109	Emma
6	Task	B	10/10/2022	29/10/2022	19	Farah
7	Task	B	10/10/2022	29/10/2022	19	George
8	Task	B	10/10/2022	29/10/2022	19	Hayley
9	Task	B	10/10/2022	29/10/2022	19	India
10	Task	B	10/10/2022	29/10/2022	19	Julie
11	Task	C	15/09/2022	20/10/2022	35	Kelly
12	Task	C	15/09/2022	20/10/2022	35	Liam
13	Task	C	15/09/2022	20/10/2022	35	Mike
14	Task	C	15/09/2022	20/10/2022	35	Natalie
15	Task	C	15/09/2022	20/10/2022	35	Oscar
16	Task	D	25/10/2022	25/11/2022	31	Pete
17	Task	D	25/10/2022	25/11/2022	31	Rachel
18	Task	D	25/10/2022	25/11/2022	31	Stef

[mangaus1111 · Accepted solution]

Hi @MORGANC846 ,

1) you have to create this snapshot table:

Snapshot_Table =

GENERATE(

         'Facts',

         DATESBETWEEN(

                      'Date'[Date],

                      'Facts'[Start Date], 'Facts'[End Date]

                      )

)

2) create this measure:

CountDays = COUNTROWS('Snapshot_Table')

3) create a relationship between the Date Table and the Snapshot_Table

Finally you can slice the column [Year-Month] from the Date Table in this way

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

[mangaus1111]

Hi @MORGANC846 ,

see my pbi file. Let me know if the link does not work.

https://1drv.ms/u/s!Aj45jbu0mDVJiyIF6BMMp6YfTJLq?e=82WjjQ

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

[MORGANC846]

Hi,

Thank you for your reply, unfortuneately the link is restricted by my work security.