cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to count all clients beyond/without two last months?

I have found:

all clients - CALCULATE(COUNTA('Outflow'[ID]);ALLSELECTED('Outflow'[LastActiveDate]))

previous month clients - CALCULATE(COUNTA('Outflow'[ID]);PREVIOUSMONTH(DATESMTD('Outflow'[LastActiveDate])))

current month clients - CALCULATE(COUNTA('Outflow'[ID]);DATESMTD('Outflow'[LastActiveDate]))

But I don't know how to count all clients beyond/without two last months?

1 ACCEPTED SOLUTION
Helper I

Eventually I did this with DAX: Clients = CALCULATE(COUNTA('Outflow'[ID]);FILTER('Outflow;'Outflow'[Date]<DATE(YEAR(today());MONTH(today())-1;1)))

5 REPLIES 5
Helper I

Eventually I did this with DAX: Clients = CALCULATE(COUNTA('Outflow'[ID]);FILTER('Outflow;'Outflow'[Date]<DATE(YEAR(today());MONTH(today())-1;1)))

Resident Rockstar

Try the below DAX formula

Clients = CALCULATE(COUNTA('Outflow'[ID]);YEAR('Outflow'[LastActiveDate]) <= YEAR(TODAY()) && MONTH('Outflow'[LastActiveDate]) <= MONTH(TODAY())-2)

Helper I

Oh! It works only for this year, previous year has shown until April (in matrix) =(((

Resident Rockstar

Try this one....

Concatenate Year and Month No. and this referes to Year(Last Active Date) and Month(LastActiveDate) respectively

ie. YearMonth =

2018 - Apr looks like  201804

2018 - Mar looks like 201803

2018 - Feb looks like 201802

.

.

.

.

2017 - Jan as 201701

2016 - Dec as 201612

2016 - Nov as 201611

Now modify the earlier DAX based on this column

`Clients = CALCULATE(COUNTA('Outflow'[ID]);YEARMONTH <= MAX(YEARMONTH) - 2)`

where YEARMONTH is the newly calculated column

Hope this solves this issue!!!

Helper I

I did this:

ClientsUntil2LM = CALCULATE(COUNTA('Outflow'[ID]);YEAR('Outflow'[LastActiveDate]) = YEAR(TODAY()) && MONTH('Outflow'[LastActiveDate]) <= MONTH(TODAY())-2)

LYsclients = CALCULATE(COUNTA('Outflow'[ID]);YEAR('Отток - всего'Outflow'[LastActiveDate])<>YEAR(TODAY()) )

Clients = [ClientsUntil2LM] + [LYsclients]

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.