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 average for month one of 1.5, and a YTD average of 1.5.  In the next month, I would have a montly average of 1.8 and a YTD average of 1.65 (Month one plus month two divided by total days).  I am able to get a sum, of those columns to work, but I am not sure how to get the YTD for the averages to work.  My fiscal year starts october 1.  I used a measure to get the YTD Sum of the number

  SumofCMI = SUM('PCC - Daily Census'[Actual CMI]) 

 

and then I created a measure that gave me the YTD for the fiscal year

 

YTDfiscal = TOTALYTD([SumofCMI],Sheet1[Date],All(Sheet1[Date]),"9/30")

 

Now I need to get the Average YTD Fiscal to work.  When I try to change the Sum in SumofCMI to average, The YTD fiscal no longer works.

 

Any hints or tips on how to do the YTD by month for data that has a record every day?  I am sure this is simple but I am new to power bi and I have been bainging this issue around for the whole week.

 

Thanks,

Peter

 

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.

     

1 Reply

  • 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.