Calculatetable to get stopped brances

[TamerOmki]

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

[johnt75]

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
)

[Poojara_D12]

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

1. Define Date Ranges: Clearly separate the date range for the current and previous month.
2. 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

[TamerOmki]

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