DAX Measure To Show Clients that Paid Last Month but Not This MOnth


#1

Hi Everyone,

I am trying to identify which of my clients paid me something last month but did not pay me anything this month.

I have written a DAX measure to show me the revenue i obtained last month and I have another DAX measure that shows me the revenue for this month.

Currently I am using a filter (Page/Report Filter) to identify the comparison

I wanted to know if I could write a measure that does the comparison for me as opposed to using page/report filters?

If it is possible, how would i go about achieving this?

DAX Measures:

Last Month = CALCULATE([This Month],DATEADD(Dates[Date],-1,MONTH))

This Month = CALCULATE([PH Revenue],FILTER(VALUES('Master Commissions'[Revenue Type]),
                COUNTROWS(FILTER('Revenue Streams','Master Commissions'[Revenue Type]='Revenue Streams'[Revenue Groups]))),
                    FILTER(VALUES(Clients[Client Entity]),
                        COUNTROWS(FILTER('Master Commissions',Clients[Client Entity]='Master Commissions'[Entity ID]))))

Thanks in Advance!!


#2

How are you looking to showcase this. It’s important in this case. The key thing it what will be the initial context on the calc.

You could do this simply like this.

But I’m guessing you want to show this differently maybe?

Also it’s quite difficult to understand unformated measures like you’ve placed in there. See here for tips on this within the forum - How Place DAX Formula Into Forum Topics & Posts


#3

Hi Sam,

Thanks for the quick response.

I’ve edited the original post and hopefully it makes it easier to understand.

I’d like to be able to showcase

  • High Level- Number of clients and revenue we should have received but didn’t (Thinking a bar chart- Open to suggestions)
  • Detailed (For the teams)-Matrix of all the clients so they can follow up

Other things I would like to achieve from this is:

  • What is still owed? (i.e-If client paid in Feb, April, May and did not pay in March)

I am trying to use your scenario Analysis to build this, hopefully on the right track.

Thanks


#4

How about these formulas.

See if the numbers work out to what you need.

Hopefully the logic is pretty self expanatory

Sales Missed = 
CALCULATE( [Sales LM],
    FILTER( ALL( Customers[Customer Name] ),
        [Sales LM] > 0 && [Total Sales] = 0 ))


Clients Not Paid = 
CALCULATE( COUNTROWS( Customers ),
    FILTER( ALL( Customers[Customer Name] ),
        [Sales LM] > 0 && [Total Sales] = 0 ))

#5

Hi Sam,

Thank you for this.

I was able to implement your DAX formula and it was exactly what I was after.

Thank You!!

I will continue to post more questions as i progress with my project!!