topic Re: Creating a Measure Start date, End date, duration in DAX Commands and Tips

Creating a Measure Start date, End date, duration

sohaibnomani — Sun, 09 Jan 2022 07:34:34 GMT

I have the following two tables, along with a Calender Table consisting of 30 calender dates (starting from 11/3/22 to 4/4/22)) with day no. (1,2,3,4,5). I have already created relationship between start date and Calender date columns. Now when i use a visual bar chart with "calender day no. " on x axis and "manpower" on Y axis.

In this case, the sum of manpower is displayed only on dates corresponding to start date only(as start date is linked with calendar date).

What I want is that the sum of manpower is displayed for the entire duration. for example If Team "MA" is working for a duration of 15 days,( row 1), then sum of manpowr of "MA" should reflect on day 1 (11/3/22) to day 15 (25/3/22)

Fact table

SR# Dep Team Start Date End Date

1	MMD	MA	11/03/2022 7:00	25/03/2022 8:00
2	MMD	MA	25/03/2022 10:00	30/03/2022 19:00
1	EMD	EA	14/03/2022 13:00	14/03/2022 15:00
2	EMD	EB	14/03/2022 13:00	14/03/2022 15:00

both are linked with "team" column.

Dimension table

Team	Manpower Type	Manpower
MA	Milwright	2
MA	Fitter	4
MA	Helper	6
MA	Scaffolder	4
MA	Rigger	1
MA	SBM	3
MA	FME	1
MA	Fabricator	0
MA	Insulator	0
MA	Technician	0
EA	Milwright	0
EA	Fitter	0
EA	Helper	0
EA	Scaffolder	0
EA	Rigger	0
EA	SBM	0
EA	FME	0
EA	Fabricator	0
EA	Insulator	0
EA	Technician	15
EB	Milwright	0
EB	Fitter	0
EB	Helper	0
EB	Scaffolder	0
EB	Rigger	0
EB	SBM	0
EB	FME	0
EB	Fabricator	0
EB	Insulator	0
EB	Technician	10

Re: Creating a Measure Start date, End date, duration

ValtteriN — Sun, 09 Jan 2022 10:36:55 GMT

Hi,

Here is one way to do this:

1. Create a new column to your fact table which displays teams manpower:

ManPowerA =

var team = TeamsFact[Team] return

calculate(SUM(TeamComposition[Manpower]),TeamComposition[Team]=team,ALL(TeamComposition))

2. Create following measure:

Manpower =

var c_date = MAX('Calendar'[Date])

return

CALCULATE(SUM(TeamsFact[ManPowerA]),

FILTER(TeamsFact,TeamsFact[SDate]<=c_date &&

TeamsFact[Edate]>=c_date))

3. End result (note that I don't have an active relationship from fact to calendar table):

The results check out with the data.

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Sun, 09 Jan 2022 12:36:45 GMT

Following is the result, which is same as before

Applied formula for column

for measure

I have linked date column from calendar table to startdateonly from the Fact table, but when select "startdateonly" in the above visual chart, no data is displayed while with "day" of Calendar table, it works.

Re: Creating a Measure Start date, End date, duration

ValtteriN — Sun, 09 Jan 2022 18:58:42 GMT

Do you need the relationship between start date and calendar for some other reason? If not you could disable it. That is likely causing the issue here.

Re: Creating a Measure Start date, End date, duration

smpa01 — Sun, 09 Jan 2022 20:25:40 GMT

sohaibnomani do you mean this?

_totalManpower = VAR _team = MAX ( _fact[Team] ) VAR _start = MAX ( _fact[Start Date] ) VAR _end = MAX ( _fact[End Date] ) VAR _tbl = FILTER ( CROSSJOIN ( 'calendar', _fact ), _start <= 'calendar'[Date] && _end >= 'calendar'[Date] ) RETURN SUMX ( ADDCOLUMNS ( _tbl, "_sum", CALCULATE ( SUM ( _dimension[Manpower] ), TREATAS ( { _team }, _dimension[Team] ) ) ), [_sum] )

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Mon, 10 Jan 2022 04:50:02 GMT

Thanks for your solution. However slight changes would be required in the code. In ur file, i have ploted _totalmanpower MEASURE against Calendar Date. I m getting results below. What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date .I would be using dates manpower basically for the plot in order to see total manpower bar graph. A team filter would be applied later

Re: Creating a Measure Start date, End date, duration

smpa01 — Mon, 10 Jan 2022 13:30:18 GMT

sohaibnomani What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date - The given dataset does not have Manpower by Date anywhere in the data? Can you revise your data?

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Wed, 12 Jan 2022 03:29:13 GMT

The fact table contains start date and end date. All the dates in between would have the same team (MA, EA, EB) while manpower is mentioned in the dimension table.

Re: Creating a Measure Start date, End date, duration

smpa01 — Wed, 12 Jan 2022 03:32:23 GMT

sohaibnomani based on the dataset that you provided what was the output you desired?

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Wed, 12 Jan 2022 03:58:05 GMT

this result. on 14th EA and EB teams are also working so total count is 46

Date	Manpower
11/03/2021	21
12/03/2021	21
13/03/2021	21
14/03/2021	46
15/03/2021	21
16/03/2021	21
17/03/2021	21
18/03/2021	21
19/03/2021	21
20/03/2021	21
21/03/2021	21
22/03/2021	21
23/03/2021	21
24/03/2021	21
25/03/2021	21

Re: Creating a Measure Start date, End date, duration

smpa01 — Wed, 12 Jan 2022 04:35:24 GMT

sohaibnomani use the following measure

Measure = SUMX ( SUMMARIZE ( ADDCOLUMNS ( FILTER ( CROSSJOIN ( 'calendar', _fact ), _fact[Start Date] <= 'calendar'[Date] && _fact[End Date] >= 'calendar'[Date] ), "sum", CALCULATE ( SUM ( _dimension[Manpower] ), TREATAS ( { CALCULATE ( MAX ( _fact[Team] ) ) }, _dimension[Team] ) ) ), [Date], [Team], [sum] ), [sum] )

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Wed, 12 Jan 2022 04:59:46 GMT

One thing that I missed to mension. If in the above fact table we have another entery of EA team doing two activities in a single day but different time, then the measure should count it once, rather than twice. Meanwhile i will try ur existing solution. THanks for your swift replies.

1	EMD	EA	14/03/2022 09:00	14/03/2022 13:00
2	EMD	EA	14/03/2022 13:00	14/03/2022 17:00

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Wed, 12 Jan 2022 13:23:45 GMT

I am unable to select "startdate" column in expression secion of "filter" command. Please guide.

Re: Creating a Measure Start date, End date, duration

smpa01 — Wed, 12 Jan 2022 13:28:40 GMT

sohaibnomani why do you want to select Start Date? follow the attached pbix

Re: Creating a Measure Start date, End date, duration

sohaibnomani — Fri, 14 Jan 2022 05:31:23 GMT

This is i am getting, I don't know what mistake i am doing. getting same results every time.

Re: Creating a Measure Start date, End date, duration

smpa01 — Fri, 14 Jan 2022 14:15:53 GMT

sohaibnomani as you can see the data that you provided and the code that I wrote return exactly you had as the picture.