Forum Discussion

sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago
Solved

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 s...
measure
  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

     

ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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!

sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago

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.

 

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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.