Forum Discussion
PowerPivot Help Needed Calculating Monthly %'s Only Up To Last Full Month
I am new to PowerPivot (5 months old) and I thought I was making good progression till I hit this snag.
The data set I am using relates to YTD competitive bids/contract awards that the business either Won or Lost (using Long Date format).
I have managed to expand the data into tables and charts like monthly Win/Lost Count, Win/Lost Percentage, Cumulative Win/Lost Count, Cumulative Win/Lost Percentage, Monthly Win/Lost % Waterfall (Bridge) Charts , Top 10 Win,Top 10 Lost, etc.
As such, when a user request came to create a column graph for individual monthly win percentages (not cumulative) that will only display up to the last full month, I did not envision that it will take me more than 5 minutes to honor; considering I had the measures I will need already created.
But for some reason, it is the nut I cannot crack. Below is what I have tried (and failed)...
Graph only displays June/July
=CALCULATE ([Won]/([Won]+[Lost]), DATEADD(FILTER(DATESYTD(Win_Loss[Actual Close Date]), SUM(Calendar[MonthNumberOfYear])>0), +5, MONTH))
Graph displays January to August
=CALCULATE ([Won%], DATEADD(Calendar[Date],-1, Month))
Since August is incomplete (in terms of calendar days) I only want the measure to display up to the last full month (i.e. Jan - July only). In September, I want it to show Jan - August only. In other words only looking back to the last full month.
Below measure appears to work by displaying up to the last full month (July). However , I would like to have a measure that will not require editing of the month number (in this instance "8"), every month.
=CALCULATE([Won%], Win_Loss[CloseMonthNumber]<8)
Any help will be greatly appreciated.
Many thanks.
9 Replies
- GilbertQ
Super User
My Measure = VAR PrevMonth = MONTH(EOMONTH(TODAY(),0)) RETURN CALCULATE([Won%], Win_Loss[CloseMonthNumber]< PrevMonth)
Hi there You could create your measure with the following:
