Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
4 years ago

Conditional column or dax expression if previous Month

Hi,

 

I nee to solve a condition. Not sure if this can be solved by creating a new column in the model.

I have millions of rows and dax expresions in my model. Assuming April'22 is the last month of my model I want to create a visual that can show me ONLY the NEW clients excluding the ones that were in last month.

 

In this case (as an example) the client 3 is the one that appears in the month (April) but wasn't in previous month. So I would need to consider only all those new ones:

 

Aditional info if its needed:

Data table (CCMS Billings append)

 

 

 

 

Model:

 

 

Date table:

 

 

Any ideas ? 

 

Thanks.

 

 

 

 

Data Protection
Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    cristianml So, in theory something like:

    Measure =
  VAR __MaxDate = MAXX(ALL('CCMS Billings append'),[Document Date])
  VAR __MaxYear = YEAR(__MaxDate)
  VAR __MaxMonth = MONTH(__MaxDate)
  VAR __LastDate = EOMONTH(__MaxDate,-1)
  VAR __LastYear = YEAR(__LastDate)
  VAR __LastMonth = MONTH(__LastDate)
  VAR __MaxClients = SELECTCOLUMNS(FILTER(ALL('CCMS Billings append'),YEAR([Document Date]) = __MaxYear && MONTH([Document Date]) = __MaxMonth),"__Client",[Master Client Name])
  VAR __LastClients = SELECTCOLUMNS(FILTER(ALL('CCMS Billings append'),YEAR([Document Date]) = __LastYear && MONTH([Document Date]) = __LastMonth),"__Client",[Master Client Name])
  VAR __Table = EXCEPT(__MaxClients, __LastClients)
RETURN
  //__Table contains list of clients that are in the current (max) month and not the month before.