cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

4 REPLIES 4
Community Support Team

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

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)```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

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

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!

Super User

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

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

Proud to be a Datanaut!

Frequent Visitor

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

@kcantor

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