Forum Discussion
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
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!
