cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kchan Frequent Visitor
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. 

 

Screen Shot 2016-08-16 at 5.31.01 PM.png

 

Thanks! 

4 REPLIES 4
Community Support Team
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)

 

 

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

 

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: | |
kchan Frequent Visitor
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
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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kchan Frequent Visitor
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!