Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sohaibnomani
Helper II
Helper II

Creating a Measure Start date, End date, duration

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.

 

sohaibnomani_0-1641713229826.png

 

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

1MMDMA11/03/2022 7:0025/03/2022 8:00
2MMDMA25/03/2022 10:0030/03/2022 19:00
1EMDEA14/03/2022 13:0014/03/2022 15:00
2EMDEB14/03/2022 13:0014/03/2022 15:00

both are linked with "team" column.

Dimension table

TeamManpower TypeManpower
MAMilwright2
MAFitter4
MAHelper6
MAScaffolder4
MARigger1
MASBM3
MAFME1
MAFabricator0
MAInsulator0
MATechnician0
EAMilwright0
EAFitter0
EAHelper0
EAScaffolder0
EARigger0
EASBM0
EAFME0
EAFabricator0
EAInsulator0
EATechnician15
EBMilwright0
EBFitter0
EBHelper0
EBScaffolder0
EBRigger0
EBSBM0
EBFME0
EBFabricator0
EBInsulator0
EBTechnician10
1 ACCEPTED SOLUTION

@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]
)

 

 

smpa01_0-1641962117207.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

15 REPLIES 15
smpa01
Super User
Super User

@sohaibnomani  do you mean this?

 

smpa01_0-1641759896734.png

_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]
    )
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

sohaibnomani_0-1641790099320.png

 

@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?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

sohaibnomani_0-1641959373065.png

 

DateManpower
11/03/202121
12/03/202121
13/03/202121
14/03/202146
15/03/202121
16/03/202121
17/03/202121
18/03/202121
19/03/202121
20/03/202121
21/03/202121
22/03/202121
23/03/202121
24/03/202121
25/03/202121

 

@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]
)

 

 

smpa01_0-1641962117207.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

 

1EMDEA14/03/2022 09:0014/03/2022 13:00
2EMDEA14/03/2022 13:0014/03/2022 17:00

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

 

sohaibnomani_0-1642138135913.png

sohaibnomani_1-1642138175282.png

 

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
ValtteriN
Super User
Super User

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))
ValtteriN_0-1641724470938.png


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):
ValtteriN_1-1641724571356.png


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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Following is the result, which is same as before

 

sohaibnomani_1-1641731659077.png

Applied formula for column

sohaibnomani_2-1641731692764.png

for measure

sohaibnomani_3-1641731721726.png

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.

sohaibnomani_0-1641731590817.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors