DAX measure to sum months but avg by year based on number of months passed within year

[sliceNdiceUup]

Hola my fellow Geeks,

Looking for some help if you could kindly sacrifice a bit of your time to help me out with a DAX measure in Excel environment please:

Below is the example I have, you can see two measure below each other:

1. NetValue:=sum(Value) 

2. MsrAvgX:=AVERAGEX(VALUES('JCTRANS ODBC'[FY-MMM]),CALCULATE([NetValue]))  

The purpose of the second measure is to show summarized numbers by month but once we get to a higher level of  FY category/dimension I want it to show Average. But here comes the tricky part, not just any average..

Currently its showing 1676/7 =239 however I would want it to divide the total 1676 by 12 and instead of 7 (number of months that so far exists within the year).

Also if I was in current Year with only so far 4 months elapsed but there is data only against 2 months I would still want the Annual data for the FY to divide the figures accumulated during the FY by 4 months.

Any help with achieving that would be greatly appreciated.Cheers!

[sliceNdiceUup · Accepted solution]

Ok took me a while but found another solution that works in Excel where I utilised the function ISFILTERED in the absence of ISINSCOPE. Below is how I've done it:

YrAvg:=if(ISFILTERED('Calendar'[FYmonth]),[NetValue],[NetValue]/[TestingVar])

where NetValue is just a simple sum of my values column

TestingVar represents getting the denominator right which was meant to be max month in Current Financial Year and 12 for closed FYs.

For that here is a bit of workaround which could be streamlined to look better:

Created 2 measures:

is before Fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))<2022))

is in fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))=2022))

And then these where reference in a Variable measure called TestingVar:

TestingVar:=VAR

VAR1 = [is before Fy22]

VAR

VAR2= [is in fy22]

return

if(MAX([FinYear])=2022,VAR2,VAR1)

[sliceNdiceUup]

Thanks amitchandak, I will check the 2nd option as that seems to have dynamic value in the denominator depending on the existing months up to date within each year..  and let you know the outcome..

[amitchandak]

@sliceNdiceUup , Try like

if(isinscope('JCTRANS ODBC'[FY]), [NetValue]/12, [NetValue])

or

if(isinscope('JCTRANS ODBC'[FY]), [NetValue]/month(max('JCTRANS ODBC'[FY])), [NetValue])

[sliceNdiceUup]

Sorry I forgot to mention that I am working in Excel 365 where ISINSCOPE formula doesn't seem to be available.