Dax query help for calculated table

PriyaJha — Tue, 17 Dec 2024 11:13:39 GMT

How to proceed with this?

Re: Dax query help for calculated table

DataNinja777 — Tue, 17 Dec 2024 12:45:05 GMT

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,

Re: Dax query help for calculated table

johnt75 — Tue, 17 Dec 2024 12:04:21 GMT

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 )

topic Re: Dax query help for calculated table in DAX Commands and Tips

Dax query help for calculated table

Re: Dax query help for calculated table

Re: Dax query help for calculated table