Power BI DAX: Dynamic Age Calculation with DATEDIFF and FORMAT


 


Question 1) raised by my youtube video viewer on 23rd,Jan 2023: This is great. Just what I was looking for. I need to check for blanks in the birthday column because my blank birthdays are causing errors. How would you do this within your code


Quick Answer to Question 1) on 23rd Jan 2023: Just a quick suggestion by following code.

Age =
    VAR MMDD_Num_Today = VALUE(FORMAT(TODAY(),"MMDD"))
    VAR MMDD_Num_BD =
        IF(ISBLANK('Measured Personal Data'[Birthday]),
            "Blank",
            'Measured Personal Data'[Birthday]
            )
    Return
    if(MMDD_Num_BD="Blank",0,
        if(
            MMDD_Num_Today>= VALUE(FORMAT(MMDD_Num_BD,"MMDD")),
            DATEDIFF('Measured Personal Data'[Birthday],TODAY(),YEAR),
            DATEDIFF('Measured Personal Data'[Birthday],TODAY(),YEAR)-1
        ))


Question 2) raised by my youtube video viewer on 21st July 2023:  In my employee tenure data, it has employee birthday, hire date and leaving date,
and race, gender, etc. Do you have any idea how to create a dynamic measure of tenure and/or age,
as well as a "tenure group" and "age group" that can change with the calendar date? I would like
to show in a line chart the changing number of active employees across time (as x-axis).
Furthermore, I would like to breakdown the number of employees by their age group or tenure group
(using age group as a legend). The age or tenure group will be dynamically change according to
the dates in the X-axis. Any suggestions are appreciated.

My suggestions:

Assumptions on what you have:
                        - an employee table with coloumns: table update(or registered) date,
                        employeeID, birthdate, hiredate and leavingdate.
                        - an calendar Date table with date, year, querter number or name, month number or name, and
                            period number ( DAX for the Period number as :
                            periodnumber = VALUE(CONCATENATE(YEAR([Date]), RIGHT("0" & MONTH([Date]), 2))) )
                        - Calendar table column date has one to many relation link with UpdateDate column of
                        employee table.

My quick solution:
                     
                        Step1 Calcaulte the measures for counting ActiveEmployee, my DAX formula as
                                
                EmpCount = CALCULATE(
                                    COUNT([EmplID]),
                                            FILTER(ALL('Date'[PeriodNumber]),
                                               'Date'[PeriodNumber] = MAX('Date'[PeriodNumber])
                                           )
                                        )                

                ActiveEmployees = CALCULATE([EmpCount], FILTER(Employee, ISBLANK(Employee[LeavingDate])))

                        Step 2: Create column measure in employee table for DynamicTenureDays as:
                            
                        DynamicTenureDays =
                IF(
                    ISBLANK([LeavingDate]),
                    IF([UpdateDate]<=[HireDate],[HireDate]-[UpdateDate],[UpdateDate]-[HireDate]),
                    [LeavingDate] - [HireDate]
                    )

                    Step 3 : Create column measure in employee table for DynamicTenureYear as:
                        
                          DynamicTenureYears = DIVIDE([DynamicTenureDays], 365.25)  
                    
                    Step 4: Create column measure in employee table for DynamicTenureGroup, for eample as:

                        DynamicTenureGroup =
                SWITCH(
                       TRUE(),
                       [DynamicTenureYears] <= 1, "0-1 years",
                       [DynamicTenureYears] <= 3, "1-3 years",
                       [DynamicTenureYears] <= 5, "3-5 years",
                       [DynamicTenureYears] <= 10, "5-10 years",
                       [DynamicTenureYears] <= 20, "10-20 years",
                       "20+ years"
                       )

            Step 5. Create Column measure in employee table for DynamicAges, for exmaple as:

                            
                DynamicAges =
                    VAR MMDD_Num_UpdateDate = VALUE(FORMAT([UpdateDate],"MMDD"))
                    VAR MMDD_Num_BD =
                        IF(ISBLANK([EmployeeBirthDate]),
                        "Blank",
                        [EmployeeBirthDate]
                        )
                    Return
                        if(
                           MMDD_Num_BD="Blank",
                           0,
                           if(
                               MMDD_Num_UpdateDate>= VALUE(FORMAT(MMDD_Num_BD,"MMDD")),
                               DATEDIFF([EmployeeBirthDate],[date],YEAR),
                               DATEDIFF([EmployeeBirthDate],[date],YEAR)-1
                                )
                            )

             Step 6. Create column measure in employee table for DynamicAgeGroup as:

                            
                    DynamicAgeGroup =
                        SWITCH(
                                TRUE(),
                                [DynamicAges] <= 30, "20-30 years",
                                [DynamicAges] <= 40, "30-40 years",ate
                                [DynamicAges] <= 50, "40-50 years",
                                "50+ years"
                                )


            Step 7. Create line charts to show the changing number of active employees across time (as x-axis) by
                                 by the breakdown of Tenure group or age group:
                                
                                Line chart creations
                                - place measure activeEmployee in Y-axis
                                - place the DynamicTenureGroup or DynamicAgeGroup in legend
                                - place year or created hierarchy (year/quarter/periodnumber) in X-axis.
                                
                            Slicer creation:
                                - place a created hierarchy (year/quarter/periodnumber) in visual field
                                - format the visual style as vertical list.

                       the solution visual looks like:









     

Comments

Post a Comment

Popular posts from this blog

Power BI DAX Formula for Dynamic Ranking Calculations Based on User Selections and Filters

Image
  The Youtube Vidoe:  Power BI DAX: Dynamic Ranking Calculations Based on User Selections and Filters         DAX formulas from the Video: Total Opp count = DISTINCTCOUNTNOBLANK('Fact'[Opportunity ID]) Total P_Revenue = SUM('Fact'[ProductRevenue]) Total F_P_Revenue = SUM('Fact'[FactoredProductRevenue]) Selected KPIs = SELECTEDVALUE('Table_KPIs'[KPI Name]) Account value rank index =      SWITCH(         TRUE(),         [Selected KPIs]="Opportunity Numbers",RANKX(ALL('Account'[Account]),[Total Opp count],,,Dense),         [Selected KPIs]="Product Revenues",RANKX(ALL('Account'[Account]),[Total P_Revenue],,,Dense),         [Selected KPIs]="Factored Product Revenues",RANKX(ALL('Account'[Account]),[Total F_P_Revenue],,,Dense)     ) Account KPI values for sorting =      SWITCH(         TRUE(),         ...
Read more

Power BI DAX and Visualizations: Active employees across time (as x-axis) by Tenure group or age group

Image
Business Case raised by my youtube channel viewer on the 21st July 2023. In my employee tenure data, it has employee birthday, hire date and leaving date, and race, gender, etc. Do you have any idea how to create a dynamic measure of tenure and/or age, as well as a "tenure group" and "age group" that can change with the calendar date? I would like to show in a line chart the changing number of active employees across time (as x-axis). Furthermore, I would like to breakdown the number of employees by their age group or tenure group (using age group as a legend). The age or tenure group will be dynamically change according to the dates in the X-axis. Any suggestions are appreciated. My suggestions: Assumptions on what we have for the case:                         - an employee table with coloumns: table update(or registered) date,              ...
Read more

Excel VBA Tips: A Macro to Generate Line Chart Dynamically for Loan Schedule Trend Report

Image
The entire code shared in this video is designed to automate the process of creating a dynamic line chart based on loan data in the "Loan Report" sheet and display the chart on the "Charts" sheet. The chart shows trends in accumulated principal, accumulated interest, and loan remaining balance over time. The code is also flexible enough to handle both yearly and monthly loan schedule reports. The VBA codes as: Sub CreateLineChart()       '1. Variable Declarations     Dim srcSheet As Worksheet     Dim chtSheet As Worksheet     Dim lastRow As Long     Dim chartObj As Shape     Dim chartDataRange As Range     Dim xAxisRange As Range     Dim yAxisRange As Range     Dim colPrincipal As Variant     Dim colInterest As Variant     Dim colRemainingBalance, ColMonth As Variant     '2. set source and chart sheets     Set srcSheet = ThisWorkbook.Worksheets("Loa...
Read more