How to calculate Actual and next quarter forecasts in power BI using DAX

Question

I'm trying to write DAX to calculate something like this below. I have a Date Calendar named (Date Table) which has Quarter, month, year info. I have some measures and trying to calculate something like if a month let's say January is selected from the slicer, it should populate the following power bi card like below. I'm unable to create a measure for the card which should calculate the amount for Q1 Forecast (Feb-Mar) if you are in January 2021. I have tried the following measures but not sure they work. The MTDForecast(Month to Date) measure works and not included in this post but not sure how to calculate and display calculations for power Bi Card for Feb-Mar forecast if you are in January? If in January then calculate the forecast for February and March?

Q1 Forecast (Feb-Mar)

NextForecast = IF(MIN('Date Table'[Quarter]) = "Q1", [Month] = "January" , CALCULATE(table[MTDForecast], ALL('Date Table'),  'Date Table'[Quarter] = "Q1"),

                  IF(MIN('Date Table'[Quarter]) = "Q1",[Month] IN {"February", "March"}, CALCULATE(table[MTDForecast], ALL('Date Table'),  'Date Table'[Quarter] = "Q1")))

                 

Thanks in advance!

Answer 1

Here example of my calculation where I show always currentMonth -> EndOfQuarterMonth (I can do this even simpler, but it depends on my specific calendar). If you have day in calendar, then you can reproduced this statement.

ForcastRange = var __quarter = 
CALCULATE( max('Calendar'[Calendar[Op Quarter]]]), 'Calendar'[Calendar[issue_date]]] = TODAY()) 
var __currentMonth = CALCULATE( MAX('Calendar'[Calendar[Op Month]]]), 'Calendar'[Calendar[issue_date]]] = TODAY()) 
var __MaxDayQuarter = CALCULATE( MAX('Calendar'[Calendar[issue_date]]] ), FILTER(ALL('Calendar'), 'Calendar'[Calendar[Op Quarter]]] = __quarter ))
var __MaxMonth = CALCULATE( MAX('Calendar'[Calendar[Op Month]]]), 'Calendar'[Calendar[issue_date]]] = __MaxDayQuarter) 
var __result = CONCATENATE(__currentMonth , __MaxMonth)
return __result