Dax Counts at end of each month

Question

I have a measure that depending on a "before" date slicer shows how many accounts were active at any given point in the company's history. I'm being asked to show month over month growth (end of month 1 compared to end of month 2 totals) but that's difficult given my measure needs a date slicer with one date value to return a total.

Active_Accounts =
CALCULATE (
    COUNTX (
        FILTER (
            VALUES ( 'TEST CHARGES'[BI_ACCT] ),
            [total as of date] > 0
        ),
        [BI_ACCT]
    )
)

Sample Table of Dashboard

link to sample file

https://www.dropbox.com/s/pewpm85wogvq3xf/test%20active%20charges.pbix?dl=0

if you move the slider you'll see the active accounts total change to show at that time in history how many accounts had an active charge. What I'm hoping to add to the dashboard is a measure that can be placed on a table of month end values and show the active accounts at that time so I can do month to month comparisons.

Example of desired data format

    Active Accounts = 
var month_end =
 ENDOFMONTH (
    LASTNONBLANK (
        'Test Charges Date Table'[Date],
        CALCULATE ( DISTINCTCOUNT( ( 'TEST CHARGES'[BI_ACCT] ) )
         )
    )
)

var last_date = 
CALCULATE( 
    LASTNONBLANK('TEST CHARGES'[CHG_DATE], ""),
     'TEST CHARGES'[CHG_DATE] <= max('Test Charges Date Table'[Date])
)

var num_of_actives =
CALCULATE(
    Countx(
            Filter(
                Values('TEST CHARGES'[BI_ACCT]),
                 [total as of date] > 0
            ) , [BI_ACCT]
        ),
    last_date <= month_end
)

return num_of_actives

user_19042379 · Answer

As Peter advices you do not need Calculate() to show total in the card and using of Calculate() reduces speed of calculation. In your case speed is reduced with no reason.

There are no need to have a special column for month - use date hierarchy for row and just exclude day and quater levels. Then add the measure to the visual table/matrix

Cummulative Count = 
    Calculate(
        [Active_Accounts]
        ,'Test Charges Date Table'[Date]<=MAX('Test Charges Date Table'[Date])
    )

Cummulative Count prevMonth = 
        Calculate(
            [Cummulative Count]
            ,PARALLELPERIOD('Test Charges Date Table'[Date],-1,MONTH)
        )

Dax Counts at end of each month

1 Answers