DAX Help

[Iyke4joe]

Please I need help converting the SQL Code below to Power BI DAX. a and b aliases are for different tables respectively. Thank you.

(((COUNT(a.[Case ID])*365/MAX(b.[Days in Month])))/(MAX(b.Membership)/1000))
/
CASE WHEN Month(GetDate())=1 THEN 12
ELSE Month(DateAdd(mm, -1, Getdate()))
  END AS [Admits/K]
,(SUM(a.SaleDays)*365/MAX(b.[Days in Month]))/(MAX(b.Membership/1000))
/
CASE WHEN Month(GetDate())=1 THEN 12
ELSE Month(DateAdd(mm, -1, Getdate()))
  END AS [Days/K]

[Iyke4joe]

@Greg_Deckler 

Hi Greg, thank you for your response. For the sake of security, I can't give the exact data but here's a typical representation of it.

Thank you in anticipation of your response.

Table A
CaseID	MemberID	SaleDays	Date
234	cc1	4	3/6/2019
456	cc2	3	4/12/2019
789	cc3	9	5/19/2019

Table B
Member_id	Membership	Year	Month	Days in Month
cc1	2	2019	3	3
cc2	3	2019	4	30
cc3	5	2019	5	31
cc4	9	2019	7	31
cc5	7	2019	8	31

[Iyke4joe]

@Greg_Deckler 

Hey Greg, thanks for your response and I apologize for just getting back to you on this.

I have asked and could not get any right explanation for the logic behind the SQL code. I am just responsible to create report and dashboard on power BI using the Code provided by the DBA team. I ahve played with this in different ways to avoid using the end column but still end up needing to use it. If there's anyway you can help, I will appreciate this. I don't know if they are doing it per every 1000 membership( just a hunch). Pls help as this is the only thing holding me back from moving forward.

Thank you

[Greg_Deckler]

@Iyke4joe - Yep, understood, sample data is great. And what is the expected output from the sample data you provided? Once I understand that, should be able to mock this up in a PBIX for you.

[Iyke4joe]

@Greg_Deckler 

Here's the explanation I got for the code. Will this help?

Metric	Membership	Days in Year	Days in Month	Formula	Comment	Solution/ K
315	12821	365	31	=(A2*C2/D2)/(B2/1000)	(metric * Days in Year/Days in Month)/(Membership/1000)  ; Goes down to days and helps in Feb since it has fewer days	289.28

[Greg_Deckler]

@Iyke4joe - Honestly, this is generally not the best way to go about this. It's far easier to start with source data example, expected output instead of trying to parse SQL code. That said, equivalent of CASE statement is SWITCH.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.