Dax query help for calculated table

[PriyaJha]

In Power BI, I have a table,

Month | Name
Sep | R1
Sep | R2
Sep | R3
Oct | R1
Oct | R4
Nov | R1
Nov | R5

I need to create a calculated table to show the names, who is missing for in the current month as compared to previous month

E.g.
Month | Name
Oct | R2
Oct | R3
Nov |R4

How to proceed with this?

[DataNinja777 · Accepted solution]

Hi @PriyaJha ,

To create a calculated table in Power BI that identifies names missing in the current month compared to the previous month, you first need to create a calculated calendar table to use in your data model, as your Month column only show text value and Power BI will not be able to recognize it as short month name.  

Next, you will need to add a calculated column in your table like below in order to create a many-to-one relationship with your calculated calendar table. 

FullDate = 
DATE(2023, 
    SWITCH(
        'Table'[Month],
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        BLANK()
    ), 
1)

Next you can create a calculated table which produces your required output. 

MissingNames = 
VAR AllNames = DISTINCT('Table'[Name])
VAR AllDates = DISTINCT('Table'[FullDate])
VAR AllCombinations =
    CROSSJOIN(
        SELECTCOLUMNS(
            AllDates,
            "Month", FORMAT([FullDate], "MMM"),
            "FullDate", [FullDate]
        ),
        AllNames
    )
RETURN
FILTER(
    AllCombinations,
    VAR CurrentMonth = [FullDate]
    VAR PrevMonthNumeric = MONTH(CurrentMonth) - 1
    VAR PrevYear = YEAR(CurrentMonth) - IF(PrevMonthNumeric = 0, 1, 0)
    VAR PrevMonthAdjusted = IF(PrevMonthNumeric = 0, 12, PrevMonthNumeric)
    VAR Previous_Month = DATE(PrevYear, PrevMonthAdjusted, 1)
    VAR NamesInCurrentMonth =
        CALCULATETABLE(
            VALUES('Table'[Name]),
            'Table'[FullDate] >= CurrentMonth,
            'Table'[FullDate] < EOMONTH(CurrentMonth, 0) + 1
        )
    VAR NamesInPreviousMonth =
        CALCULATETABLE(
            VALUES('Table'[Name]),
            'Table'[FullDate] >= Previous_Month,
            'Table'[FullDate] < CurrentMonth
        )
    RETURN
        NOT([Name] IN NamesInCurrentMonth) &&
        [Name] IN NamesInPreviousMonth
)

This DAX formula calculates a table of missing names that appeared in the previous month but not in the current month. It starts by retrieving distinct names and distinct full dates. It crossjoins these two lists to produce all combinations of months and names. For each combination, it determines the current month’s date, calculates the first day of the previous month, and then uses CALCULATETABLE to find which names appeared in the current month and which names appeared in the previous month. Finally, it applies a filter that returns only those names that were present previously but are missing now.

I have attached an example pbix file for your reference. 

Best regards,

[johnt75 · Accepted solution]

Make sure that you have a date table, marked as a date table, linked to your main fact table. Then you can use

Missing names =
GENERATE (
    VALUES ( 'Date'[Year month] ),
    VAR CurrentNames =
        VALUES ( 'Table'[Name] )
    VAR PrevNames =
        CALCULATETABLE ( VALUES ( 'Table'[Name] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
    VAR Result =
        EXCEPT ( PrevNames, CurrentNames )
    RETURN
        Result
)

[DataNinja777 · Accepted solution]

Hi @PriyaJha ,

To create a calculated table in Power BI that identifies names missing in the current month compared to the previous month, you first need to create a calculated calendar table to use in your data model, as your Month column only show text value and Power BI will not be able to recognize it as short month name.  

Next, you will need to add a calculated column in your table like below in order to create a many-to-one relationship with your calculated calendar table. 

FullDate = 
DATE(2023, 
    SWITCH(
        'Table'[Month],
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        BLANK()
    ), 
1)

Next you can create a calculated table which produces your required output. 

MissingNames = 
VAR AllNames = DISTINCT('Table'[Name])
VAR AllDates = DISTINCT('Table'[FullDate])
VAR AllCombinations =
    CROSSJOIN(
        SELECTCOLUMNS(
            AllDates,
            "Month", FORMAT([FullDate], "MMM"),
            "FullDate", [FullDate]
        ),
        AllNames
    )
RETURN
FILTER(
    AllCombinations,
    VAR CurrentMonth = [FullDate]
    VAR PrevMonthNumeric = MONTH(CurrentMonth) - 1
    VAR PrevYear = YEAR(CurrentMonth) - IF(PrevMonthNumeric = 0, 1, 0)
    VAR PrevMonthAdjusted = IF(PrevMonthNumeric = 0, 12, PrevMonthNumeric)
    VAR Previous_Month = DATE(PrevYear, PrevMonthAdjusted, 1)
    VAR NamesInCurrentMonth =
        CALCULATETABLE(
            VALUES('Table'[Name]),
            'Table'[FullDate] >= CurrentMonth,
            'Table'[FullDate] < EOMONTH(CurrentMonth, 0) + 1
        )
    VAR NamesInPreviousMonth =
        CALCULATETABLE(
            VALUES('Table'[Name]),
            'Table'[FullDate] >= Previous_Month,
            'Table'[FullDate] < CurrentMonth
        )
    RETURN
        NOT([Name] IN NamesInCurrentMonth) &&
        [Name] IN NamesInPreviousMonth
)

This DAX formula calculates a table of missing names that appeared in the previous month but not in the current month. It starts by retrieving distinct names and distinct full dates. It crossjoins these two lists to produce all combinations of months and names. For each combination, it determines the current month’s date, calculates the first day of the previous month, and then uses CALCULATETABLE to find which names appeared in the current month and which names appeared in the previous month. Finally, it applies a filter that returns only those names that were present previously but are missing now.

I have attached an example pbix file for your reference. 

Best regards,