Calculating last 6 month average but ignoring current month

[Anonymous]

Hello,

I am relatively new to Power BI. I use the following measure to calculate last 6 months average, excluding current month:

6MonthResolvedAVG =
CALCULATE(SUMX('Data Table',IF(DATEDIFF('Data Table'[Date],TODAY(),MONTH) >0,[ResolvedCR],0)),
DATESINPERIOD('Data Table'[Date],EOMONTH (lastdate('Data Table'[Date]) , -1 ),-6,MONTH))/6​

It works correctly for recent data. However.... if I apply some filters and there is no current data but , let's say data is from 2019, then the measure still calculates something, which is incorrect. I need to force the measure to use maximum date from the table as of today and not a "relative" date depending on the filters.

Expected behavior: if there isn't any data for the past 6 months,excluding current month and calculated from absolute maximum date in the table, then display 0.

Please advise.

Regards,

M. R.

[AlexisOlson]

I'd suggest calculating the end of the last month as a variable first and then using in your date filtering.

6MonthResolvedAVG =
VAR PrevMonth =
    EOMONTH ( CALCULATE ( MAX ( 'Data Table'[Date] ), ALL ( 'Data Table' ) ), -1 )
RETURN
    CALCULATE (
        [ResolvedCR],
        DATESINPERIOD ( 'Data Table'[Date], PrevMonth, -6, MONTH )
    ) / 6​

[Anonymous]

Thank you Alexis but [ResolvedCR] is a measure, not a table column and I get "Cannot find name '[ResolvedCR]'".

Can you please advise how to resolve this issue?

[AlexisOlson]

That error doesn't make sense to me. If the measure exists, it should be referencing it just like it did in your version.

Did the measure name change perhaps?

[Anonymous]

Measure did not changed but after looking at some older posts, it seems that referencing a measure in another measure may not work:

[AlexisOlson]

You can absolutely reference measures within other measures. Can you show a screenshot of your original measure that doesn't have an error but references the same measure? I'm really curious what the difference is.

[Anonymous]

This is the measure:

ResolvedCR = if('Combined_CQ_view'[DateStamp]="Resolved",1,0)

[AlexisOlson]

That's not a correctly written measure. It appears to be a calculated column, which might explain why it worked in your SUMX but not when called as a measure.

[Anonymous]

This is the original calculation, no error:

6MonthResolvedAVG =

CALCULATE(SUMX('Combined_CQ_view',IF(DATEDIFF('Combined_CQ_view'[Date],TODAY(),MONTH) >0,[ResolvedCR],0)),

DATESINPERIOD('Combined_CQ_view'[Date],EOMONTH ( lastdate('Combined_CQ_view'[Date]) , -1 ),-6,MONTH))/6

[AlexisOlson · Accepted solution]

OK. It makes sense now. You've defined a calculated column 'Combined_CQ_view'[ResolvedCR] instead of a measure.

Therefore you need to adjust the formula. Like this, for example:

6MonthResolvedAVG =
VAR PrevMonth =
    EOMONTH ( CALCULATE ( MAX ( 'Combined_CQ_view'[Date] ), ALL ( 'Combined_CQ_view' ) ), -1 )
RETURN
    CALCULATE (
        SUM ( 'Combined_CQ_view'[ResolvedCR] ),
        DATESINPERIOD ( 'Combined_CQ_view'[Date], PrevMonth, -6, MONTH )
    ) / 6​

[Anonymous]

It works now, thank you very much for your help !

[amitchandak]

@Anonymous , Try like

6MonthResolvedAVG =
if( CALCULATE(distinctcount('Date'[Month Year]),
DATESINPERIOD('Data Table'[Date],EOMONTH (max('Data Table'[Date]) , -1 ),-6,MONTH) , filter('Data Table',not(isblank([ResolvedCR])) )) <=6,0, CALCULATE([ResolvedCR],
DATESINPERIOD('Data Table'[Date],EOMONTH (max('Data Table'[Date]) , -1 ),-6,MONTH))/6​)

[Anonymous]

Thank you Amit, but what is 'Date'[Month Year]? I don't have this table. Should I create it?