topic Re: Rolling 6 months in DAX Commands and Tips

Rolling 6 months

jimpatel — Tue, 21 May 2024 11:40:41 GMT

Hi,

Thanks a lot for looking at my post.

I am using below code (Thanks to Forum community). May i know how can i change this to show 6 months rolling average please?

Thanks a lot

Final result = VAR _date = SELECTEDVALUE ( 'Table'[Due Date] ) VAR _partmonth = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) && 'Table'[Time to Close] = 2 ) ) VAR _month = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) ) ) RETURN IF ( ISBLANK ( _partmonth ), 0, DIVIDE ( _partmonth, _month ) )

Re: Rolling 6 months

lbendlin — Wed, 22 May 2024 00:34:01 GMT

That formula only tells you how far you got in a particular month (how many days in you are), and it's rather inefficient. You could have just used the day number and divide it by the count of days in the month.

A 6 month rolling average would not make sense in this context.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Rolling 6 months

Anonymous — Fri, 31 May 2024 06:04:17 GMT

HI jimpatel,

You can try to use the following measure formulas if it suitable for your requirement:

formula = VAR _currDate = MAX ( 'Table'[Due Date] ) VAR summary = SUMMARIZE ( FILTER ( ALLSELECTED ( 'Table' ), [Due Date] >= DATE ( YEAR ( _currDate ), MONTH ( _currDate ) - 6, DAY ( _currDate ) ) && [Due Date] <= _currDate ), [Due Date], "Div", VAR cDate = MAX ( 'Table'[Due Date] ) RETURN CALCULATE ( DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Time to Close] = 2 ), COUNTROWS ( 'Table' ) ), ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Due Date] ) = YEAR ( cDate ) && MONTH ( 'Table'[Due Date] ) = MONTH ( cDate ) ) ) RETURN AVERAGEX ( summary, [Div] )

Regards,

Xiaoxin Sheng

Re: Rolling 6 months

jimpatel — Mon, 03 Jun 2024 09:26:39 GMT

Thanks a lot for your effort and time. But this formula does not work well for me. When i apply above formula to get last 6 months rolling average, i am getting below answers which is not right.

Any idea please?

thanks a lot

Re: Rolling 6 months

lbendlin — Mon, 03 Jun 2024 13:18:55 GMT

Second request: Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523