PowerPivot Help Needed Calculating Monthly %'s Only Up To Last Full Month

[LowKeyBoy]

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.

[GilbertQ]

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:

[LowKeyBoy]

Thanks GilbertQ.

I got an error message related to the 'RETURN' function (below). Kindly advise if I did something wrong.

 

[GilbertQ]

Hi there,

I cannot see the image for some reason, could you possibly post the actual details?

[LowKeyBoy]

Sure. I used the measure you provided and received an error message as follows:

"The expression is not valid or appears to be incomplete. Please review and correct the expression. The syntax for 'RETURN' is not correct"

[v-piga-msft]

Hi @LowKeyBoy,

Could you share the screenshots about the formula and the error message?

Best Regards,

Cherry

[LowKeyBoy]

Please find image below. Many thanks

[v-piga-msft]

Hi @LowKeyBoy,

It seems that you are using Pivot table in Excel?

Based on your screenshots, it seems that you haven't store the result Month(Eomonth(Today(),0)) with VAR so your syntax is incorrect for RETURN.

In addition, please note the VAR function is supported for SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only.

Best Regards,

Cherry

[LowKeyBoy]

Thanks Cherry; yes I am using PowerPivot in Excel 2013...

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.

[GilbertQ]

This should be able to do it

My Measure =
VAR MonthNumber = MONTH(TODAY())
RETURN
CALCULATE([Won%], Win_Loss[CloseMonthNumber] < MonthNumber )