Forum Discussion

petermb72's avatar
petermb72
Icon for Helper IV rankHelper IV
7 years ago
Solved

How to get a Fiscal YTD Average Column

I have a table of data that gives me a number every day of the year.  I need to get a monthly average of the 30 days, then I need to get a fiscal YTD number for each month.  So I would have an averag...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi petermb72 

     

    Not sure if you'd like to get below result:

    Average YTD = var currentday = MAX('Table'[Date])
var currentyear = YEAR(MAX('Table'[Date]))
var daysnum = IF(currentday<=DATE(currentyear,9,30),DATEDIFF(DATE(currentyear-1,9,30),currentday,DAY)+1,DATEDIFF(DATE(currentyear,9,30),currentday,DAY)+1)
Return 
DIVIDE([YTDfiscal],daysnum)

    Pbix attached.

     

v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi petermb72 

 

Not sure if you'd like to get below result:

Average YTD = var currentday = MAX('Table'[Date])
var currentyear = YEAR(MAX('Table'[Date]))
var daysnum = IF(currentday<=DATE(currentyear,9,30),DATEDIFF(DATE(currentyear-1,9,30),currentday,DAY)+1,DATEDIFF(DATE(currentyear,9,30),currentday,DAY)+1)
Return 
DIVIDE([YTDfiscal],daysnum)

Pbix attached.