cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jānis Frequent Visitor
Frequent Visitor

Calculated table dynamically change from new data

Hello,

 

I have data (in table):

1.PNG

 

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 the end of december (47+27+19)/3. I will show it with Line chart and need to calculate age for every employee at the end of each month in active period.

 

How could I calculate those data? I assume that I need to calculate table...

Or maybe there is other solution.

 

I will appreciate any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: Calculated table dynamically change from new data

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

Capture.JPG

 

Regards,

Charlie Liao

 

View solution in original post

3 REPLIES 3
Moderator v-caliao-msft
Moderator

Re: Calculated table dynamically change from new data

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

Capture.JPG

 

Regards,

Charlie Liao

 

View solution in original post

Jānis Frequent Visitor
Frequent Visitor

Re: Calculated table dynamically change from new data

Thank you, @v-caliao-msft!

 

Almost working. I'm still cheking solution (new with DAX).

I found, that I need one more filter - I need to include only active employees, so I need to filter from "Employment Start Date" too, but not sure - is it correct.

 

AverageAge =
DATEDIFF (
    CALCULATE (
        AVERAGE ( Table2[BirthDate] ),

        FILTER (

            Table2, Table2[Employment Start Date] < 'LastDayofMonth'[LastDay]

            ),
        FILTER (
            Table2,
            OR (
                ISBLANK ( Table2[InactiveDate] ),
                Table2[InactiveDate] > 'LastDayofMonth'[LastDay]
            )
        )
    ),
    'LastDayofMonth'[LastDay],
    YEAR
)

 

Jānis Frequent Visitor
Frequent Visitor

Re: Calculated table dynamically change from new data

I needed to make some changes, but in generally it is what I needed.

 

Thank you, @v-caliao-msft

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 351 members 3,235 guests
Please welcome our newest community members: