Using PREVIOUSMONTH works for most cases but need conditional logic for January only

[kchan]

Hello,

The previousmonth function captures what I need for 11 out of the 12 months of the year.  January is the special case which needs to be handled differently.  My formulas:

Incurred = ActualSpend - ActualSpend_PrevMonth

ActualSpend_PrevMonth = IF(ISBLANK([Max Spend]), 0, CALCULATE([Max Spend], PREVIOUSMONTH('Calendar'[Date])))

Incurred_jan = CALCULATE([Max Spend], FILTER('Calendar', 'Calendar'[Date].[Month] = "January")) -MAX(SomeOtherSpreadsheet[ITD Actual To EOPY])

There's an actualspend value for January but i need to subtract it from a starting value to figure whats actually been spent in January.  

Is there an easy way to merge the outputs so I can visualize it on a barchart/table?   Essentially I need that January value with the rest of the data. 

Thanks! 

[Anonymous]

Hi kchan,

Based on my understanding, you want to merge above measures to one measure, right?

If as I said, you can try to use If() function to decide whether the month is “January”, then return Incurred_jan or Incurred values.

Incurred = 

var ActualSpend_PrevMonth = IF(ISBLANK([Max Spend]), 0, CALCULATE([Max Spend], PREVIOUSMONTH('Calendar'[Date]))) 

return 

if('Calendar'[Date].[Month]= "January",

CALCULATE([Max Spend], FILTER('Calendar', 'Calendar'[Date].[Month] = "January")) -MAX(SomeOtherSpreadsheet[ITD Actual To EOPY]) , 

ActualSpend - ActualSpend_PrevMonth)

If above formula not meet your requirement, could you please provide data structure about your tables for our analysis?

Regards,

Xiaoxin Sheng

[kchan]

Thanks for the reply!  I'm running into an issue with the IF statement on the month.   

IF ('Calendar'[Date].[Month]= "January")

yields this error:

A single value for variaton 'Month' for column 'Date' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Data Structure:

Calendar is the your typical Date Dimension table.  The rest of the structure is quite simple.  We report in Cumulative totals so I'm trying to find the monthly spend. January is special case as there's no previous month  To be completely correct I should take the spend value based on the latest reporting date each month but the max spend for that month should be suffice.  

Reporting Period, Monthly Spend
4/1/2015, $600,000.00
3/9/2015, $500,00.00
2/17/2015, $400,000.00
2/4/2015, $300,000.00
1/11/2015, $200,000.00   

I setup a relationship between Reporting Period and my Calendar table. 

Hope this helps!

[kcantor]

It looks like you will need to create this as a measure instead of as a column.

[kchan]

@kcantor

Double checked my work and they were created as measures.  I appreicate the reply!