Forum Discussion

Frequent Visitor

8 years ago

Solved

Calculated table dynamically change from new data

Hello, I have data (in table): I need to calculate and show, what is average employees age at the end of each month in period (for example in period 2017-09 - 2017-12). In example at th...

Need Help

v-caliao-msft
8 years ago
Jānis,

Create a date table to return the last day of each month.

Create a date table

Date = CALENDAR(DATE(2015,1,1),DATE(2017,12,31))

Create a column to get the last day of each month
LastDay = ENDOFMONTH('Date'[Date])

Create another table
LastDayofMonth = SUMMARIZE('Date','Date'[LastDay])

Create a column to get average age.
AverageAge =
DATEDIFF (
    CALCULATE (
        AVERAGE ( Table2[BirthDate] ),
        FILTER (
            Table2,
            OR (
                ISBLANK ( Table2[InactiveDate] ),
                Table2[InactiveDate] > 'LastDayofMonth'[LastDay]
            )
        )
    ),
    'LastDayofMonth'[LastDay],
    YEAR
)

Regards,

Charlie Liao

sample20180104.pbix182 KB

v-caliao-msft

Microsoft Employee

8 years ago

Jānis,

Create a date table to return the last day of each month.

Create a date table

Date = CALENDAR(DATE(2015,1,1),DATE(2017,12,31))

Create a column to get the last day of each month
LastDay = ENDOFMONTH('Date'[Date])

Create another table
LastDayofMonth = SUMMARIZE('Date','Date'[LastDay])

Create a column to get average age.
AverageAge =
DATEDIFF (
    CALCULATE (
        AVERAGE ( Table2[BirthDate] ),
        FILTER (
            Table2,
            OR (
                ISBLANK ( Table2[InactiveDate] ),
                Table2[InactiveDate] > 'LastDayofMonth'[LastDay]
            )
        )
    ),
    'LastDayofMonth'[LastDay],
    YEAR
)