Forum Discussion

GunnerJ's avatar
GunnerJ
Post Patron
4 years ago

Trouble Counting Accounts based off Measure

I'm trying to show active customers by those with active charges. I have a running total in my query that shows if they have an active charge where anything above 0 is active. I created a measure "total as of date" which compares to the date table filter and returns the running total as of the max possible date. 

 

In the screenshot below you'll see the "Total as of date" shows '0' because the last dated row has that value. That means there were no active charges as of that date. If you slide the date filter to Aug 28th, 2020 it'll change to '6' and the "Total Active Test" column in the table will turn to '1' indicating the account is active.

 

"Total Active Test" in the card acts strangely. It's goal is to show the total number of active accounts as of the date filter. I'm really unsure why it appears to work in the table but not the card. There are no account filters present and at today's date I'd expect around 33k active accounts. 

 

link to sample file

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

total as of date = 
var last_date = CALCULATE( LASTNONBLANK('TEST CHARGES'[CHG_DATE], ""), 'TEST CHARGES'[CHG_DATE] <= max('Test Charges Date Table'[Date]))
return CALCULATE(min('TEST CHARGES'[RUNNING_TOTAL]), 'TEST CHARGES'[CHG_DATE] = last_date)
Total Actives Test = 
var last_date = CALCULATE( LASTNONBLANK('TEST CHARGES'[CHG_DATE], ""), 'TEST CHARGES'[CHG_DATE] <= max('Test Charges Date Table'[Date]))
 return CALCULATE(DISTINCTCOUNT('TEST CHARGES'[BI_ACCT]), filter('TEST CHARGES', [total as of date] > 0 && 'TEST CHARGES'[CHG_DATE] = last_date))

My ultimate goal is to be able to show on an individual account level if it was active based on the date filter (needs to be "before" setting) and then in a card show the total number of accounts active at any given time. I think my measures are closed but I just can't get the last bit to work.  Any help is appreciated. 

 

2 Replies

  • GunnerJ , Try measure like

     

    Total Actives Test =
    var last_date = CALCULATE( LASTNONBLANK('TEST CHARGES'[CHG_DATE], ""), 'TEST CHARGES'[CHG_DATE] <= max('Test Charges Date Table'[Date]))
    return calculate(Countx(Filter(Values('TEST CHARGES'[BI_ACCT]), [total as of date] > 0) , [BI_ACCT]) , filter('TEST CHARGES','TEST CHARGES'[CHG_DATE] = last_date))

    • GunnerJ's avatar
      GunnerJ
      Post Patron

      amitchandak 

      unfortunantly when I place that measure in a card I still get '1' as the returned value. Possibly just indicating that there are active accounts but not getting to the point where it counts them?

       

      Thank you for the quick response!