I've spent around 3 hours trying to get this one formula to work but just can't get it to work right.
I'm trying to create a 3 month rolling monthly average that doesn't require all the months to be displayed to calculate (ie filter all()). (ie if i am only filtering on 2016, Jan should still show a 3 month average from Oct-Dec 2015, not only Jan results because of the year filter).
The measure is a distinct count of opportunities (row of data) for the month.
I've tried averagex but I was getting a daily average, not an average based on the total count of the month.
The closest I have gotten to is to try and sum up the months previous and divide by 3, but some of the months just don't look right.
A few options I have tried are: (at this am just trying to do the sum so I can just easily do a divide by 3)
3 Month Avg = CALCULATE([# Opps],Summarize( FILTER ( ALL ( vw_dimdate ), vw_dimdate[date] > ( MAX ( vw_dimdate[date] ) - 3 ) && vw_dimdate[date] <= MAX ( vw_dimdate[date] ) ),vw_dimdate[fmonthkey],vw_dimdate[fmonth]) )
3 Month Avg = CALCULATE(counta(vw_fact_opportunity[opportunity_id]),all(vw_dimdate),DATESINPERIOD(vw_dimdate[date],LASTDATE(vw_dimdate[date]),-3, Month))
Any help/guidance would be appreciated.
Solved! Go to Solution.
I am new to Power BI and DAX.
I beleive you are using this as a measure.
I am trying to get this to work but I cannot seem to get it to sum over a 12 month period. Sumrec is a measure that is sum(received). I am not sure why it is not totaling. when I test the lastdate() Fx I get 4/21/2016 and the first date is 1/2/2012. I hope this is enough information to help solve what I am missing.
Any help is much appreciated.
@BrentL What table are the Year and Month columns in?
If a Calendar Table you have to point the time functions to CalendarTable[Date]
I was not using a year and month column it was a date column with a heirarchy applied minus the quarter and day. Should I make a month and year column and use that instead? Do I then point to the month column?
where is the sumrec measure? in the Receipts table as well?
what date did you use to create the hierarchy?
This formula should work! it's basically your 12 month trailing total
@Sean Thank you for the help on this. The picture below shows the table I am using for all the fields with the Sumrec formula. The "receipt date" is the date out of the system. The "received" field is the quantity we received. The other two are "New Measures" Using the "receipt date and "received" Fields. Thank you again for all the help.
What is the formula you have for the Total Sales? That maybe where I am off.
@BrentL You actually do need a Calendar Table!
It seems it works without Calendar table only when the you are using DAY aggregation
Its better to have one in your model anyway - all time intelligence functions require one! (which actually these are part of)
Here's a link provided by Matt on how to create one
This should take care of it - let me know if it doesn't.
@Sean Thank you so much for all the help. I had started making the calendar table as you sent this last reply and I got it to work. I have saved those links to my favorites so I can review and study them. It is working great now!! Much appreciation.
I'm also having the same issue.
I've created a calender as per the instruction post by Sean but it didn't work for me.
Appriciate if one of you guys can help me.
Below is my formula,
Running Total = CALCULATE([TRI],DATESINPERIOD(Calender[Date],LASTDATE(Calender[Date]),-12,MONTH))
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.