topic Re: Customer Performance Status M.O.M in DAX Commands and Tips

Customer Performance Status M.O.M

AnkitKukreja — Thu, 17 Sep 2020 09:27:58 GMT

Hi All,

I’ve created the below relationship, and I want to extract performance from the table Bounce and DPD to my master data (ECGLS), both have customer identification no as Account Number.

I want to get m.o.m performance of the customer in the matrix as per user needs against the Account_Number, Industry, Sales Manager, branch , etc i.e. all the data available in ECGLS.

What I actually want to achieve is something like this and also want my data to reflect changes as per the filters or if any other variable is used by the user instead of loan number like (Industry, Sales Manager, Branch, etc).

I've sharing the pbix file as well. Please let me know the right approach to achieve this.

https://drive.google.com/drive/folders/1yQKq8mM-8AcOsODJhaibOxoCcu7qVzkY?usp=sharing

Thanks Ankit

Re: Customer Performance Status M.O.M

amitchandak — Thu, 17 Sep 2020 09:39:55 GMT

AnkitKukreja , is MOM is Month on month. Then you can use time intelligence with date table.

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

Re: Customer Performance Status M.O.M

AnkitKukreja — Thu, 17 Sep 2020 11:08:14 GMT

Hey amitchandak

Thanks for your response. Yes it is month on month. But, this is not what i want to achieve.
May be I missed highlighting my point clearly, here by performance here i meant the payment history, whether it is paid or not paid.

So, I want to show status of payment on m.o.m basis against lan, industry, branch or any other variable. Example ( July- Bounce, Aug- Cleared, Sep- Cleared) . This is just an example.

Can you please look at my file once you've time.

Re: Customer Performance Status M.O.M

AnkitKukreja — Fri, 18 Sep 2020 05:16:39 GMT

Hi All,

I used the below measure, though i'm getting the correct results.

Would love to know any another way to achieve the same. If possible from all the experts here.

CALCULATE( SELECTEDVALUE( Bounce[BounceStatus] , “Not Presented” ) ,
CROSSFILTER( ECGLS[Account Number] , Bounce[Account_Number] , Both),
USERELATIONSHIP( ‘Calendar’[Date] , Bounce[schdate] ))

Thanks,
Ankit