Previous month totals by Subcategory

[Anonymous]

IHi All, 

Im trying to get the previous month totals . I have used the following  dax

Credentials Added Previous Month =

VAR startdate = EOMONTH(TODAY(),-2)+1

var enddate = EOMONTH(TODAY(),-1)

RETURN

CALCULATE(

COUNT(fact_cred_milestones[customer_bp]),

FILTER(ALL(fact_cred_milestones),fact_cred_milestones[milestone_date]>= startdate

&& fact_cred_milestones[milestone_date]<= enddate),fact_cred_milestones[milestone_type]="CA" )

This gives me the counts for 'CA' 

However ,In a different visual I need to show the counts of CA split into subcatgories that are part of 'CA' . Rite now the same CA count is shown for all subcategories under CA. Any help ?

[parry2k]

@Anonymous change to this:

Credentials Added Previous Month =
VAR startdate = EOMONTH(TODAY(),-2)+1
var enddate = EOMONTH(TODAY(),-1)
RETURN
CALCULATE(
COUNT(fact_cred_milestones[customer_bp]),
FILTER(ALL(fact_cred_milestones),fact_cred_milestones[milestone_date]>= startdate
&& fact_cred_milestones[milestone_date]<= enddate),
KEEPFILTERS(fact_cred_milestones[milestone_type]="CA" )
 )

BTW, when you are working with the data, the best practice is to add a date dimension and use that for any time intelligence-based calculations. It will be much faster and you will develop a more scalable solution. You can add a date dimension in your model by following my blog post here  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

✨ Follow us on LinkedIn And subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mahoneypat · Accepted solution]

Change the ALL in your FILTER from ALL(fact_cred_milestones) to

ALL(fact_cred_milestones[milestone_date], fact_cred_milestones[milestone_type])

Pat

[Anonymous]

@mahoneypat Thanks!!! It worked