Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |