SQL Case statement to DAX with Dates & mesure

[Anonymous]

CASE WHEN REPORT_DATE>DATEADD(DAY,305,MIN_MTH) THEN AVG(TOTAL_DEBT) OVER
PARTITION BY COMPANY_CODE,REGION_ID,CLUSTER_ID,MCO_ID,MSO_ID,COUNTRY_CODE,ACCOUNT_NO,CUSTOMER_NAME
RDER BY REPORT_DATE ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) END AS TOTAL_DEBT_AVG

How i can resolve above issue can any please help me.

[amitchandak]

@Anonymous , Switch True you can use

example

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

&& and

|| Or

between use && and put two condition

PARTITION BY - Summarize, groupby few more

[Mariusz · Accepted solution]

Hi @Anonymous 

I haven't work with SQL window functions for quite some time, but if you try to explain what you trying to achieve and provide a data sample then I can try to help.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz. Thanksd for your response.

Here we are finding rolling average for last 12 months 

MIN_MTH Having 2017-05-3 and we are adding 305 days when the our report date is > MIN_MTH then we are caluclting Average ofor last 12 months.

[Mariusz]

Hi @Anonymous 

The below will give you 12 months avg, you add an if condition before this expression.

Sales Rolling 12 months = 
CALCULATE(
    AVERAGEX( Sales, Sales[Quantity] * Sales[Unit Price] ),
    DATESINPERIOD( 'Calendar'[Date], MIN( 'Calendar'[Date] ) -1, -12, MONTH )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn