Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SQL Case statement to DAX with Dates & mesure

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 ...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Mariusz
Community Champion
6 years ago

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