topic Calculatetable to get stopped brances in DAX Commands and Tips

Calculatetable to get stopped brances

TamerOmki — Wed, 13 Nov 2024 11:31:15 GMT

Hi,

I'm trying to determine which branches had transactions in a specific month but had no transactions in the next month using the following DAX. but whenever I tried it, it gave a blank or no data ( no error within the Dax):

BranchesLastMonthNoThisMonth =

VAR Branch_this_M = CALCULATETABLE(

VALUES( _2024 [Branch] ),

_2024 [Branch] <> BLANK(),

FILTER(

ALL( 'Calendar' ),

'Calendar'[Date] >= MIN( 'Calendar'[Date] ) &&

'Calendar'[Date] < MAX( 'Calendar'[Date] )

)

VAR Branch_last_M = CALCULATETABLE(

VALUES( _2024 [Branch] ),

_2024 [Branch] <> BLANK(),

FILTER(

ALL( 'Calendar' ),

'Calendar'[Date] < MIN( 'Calendar'[Date] ) &&

'Calendar'[Date] >= STARTOFMONTH( DATEADD( 'Calendar'[Date], -1, MONTH ) )

)

RETURN

EXCEPT( Branch_last_M, Branch_this_M )

would you please help?

Thank you

Re: Calculatetable to get stopped brances

johnt75 — Wed, 13 Nov 2024 11:58:24 GMT

A calculated table cannot take into account any slicers or filters, it is only calculated during data refresh.

You could create a measure to return 1 if the branch had transactions in the selected month but not the prior month, and use that as a filter on the visual. If you have a measure [Transactions] which returns the number or value of transactions, you could use

BranchesLastMonthNoThisMonth = IF ( ISINSCOPE ( _2024[Branch] ), VAR CurrentMonth = [Transactions] VAR PrevMonth = CALCULATE ( [Transactions], DATEADD ( 'Calendar'[Date], -1, MONTH ) ) VAR Result = IF ( ISBLANK ( CurrentMonth ) && NOT ISBLANK ( PrevMonth ), 1 ) RETURN Result )

Re: Calculatetable to get stopped brances

Poojara_D12 — Thu, 14 Nov 2024 06:20:34 GMT

Hi TamerOmki

Your DAX formula is close but has a few issues related to date filtering that may be causing the blank results. Specifically, the date ranges you’re using in FILTER may not correctly isolate transactions from just the previous and current month. Here’s an alternative approach that should work better.

Solution

Define Date Ranges: Clearly separate the date range for the current and previous month.
Use EXCEPT: Compare the list of branches with transactions in the last month against those with transactions in the current month to get branches that had transactions only in the previous month.

Here’s how you could rewrite your measure:

BranchesLastMonthNoThisMonth = VAR CurrentMonthBranches = CALCULATETABLE( VALUES(_2024[Branch]), _2024[Transaction] <> BLANK(), FILTER( ALL('Calendar'), 'Calendar'[Date] >= STARTOFMONTH(MAX('Calendar'[Date])) && 'Calendar'[Date] <= ENDOFMONTH(MAX('Calendar'[Date])) ) ) VAR PreviousMonthBranches = CALCULATETABLE( VALUES(_2024[Branch]), _2024[Transaction] <> BLANK(), FILTER( ALL('Calendar'), 'Calendar'[Date] >= STARTOFMONTH(DATEADD(MAX('Calendar'[Date]), -1, MONTH)) && 'Calendar'[Date] <= ENDOFMONTH(DATEADD(MAX('Calendar'[Date]), -1, MONTH)) ) ) RETURN EXCEPT(PreviousMonthBranches, CurrentMonthBranches)

Notes

Make sure that MAX('Calendar'[Date]) corresponds to a specific month end date to ensure proper filtering.
Verify that the Calendar table has continuous dates and is marked as a date table in your data model. This helps functions like DATEADD work correctly.

This approach should resolve the issue and return the expected list of branches. Let me know if it works or if you need further adjustments!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Calculatetable to get stopped brances

TamerOmki — Sun, 17 Nov 2024 10:59:34 GMT

Hi Poojara_D12
Thanks a lot for your kind reply, but whenever I tried to apply this DAX I got this error "The first argument to 'STARTOFMONTH' must specify a column."

can you help please ...

Thanks once again