Issue with Forecast DAX measure

[Dshami]

I have a scenario to show the Expenses data for selected year and unselected months( if month selected as January, It should show Expenses of February to December for selected year, for multiple selections same functioning).

I have created two dax as below

1. End Date = MAX(Calendar [Date])

( Max calendar date is last day of current year)

2. Exp Forecast = CALCULATE ( SUM( Table [Sales]), DATESBETWEEN(Calendar [Date], DATE(YEAR([End Date]), MONTH ([End Date])+1,01), DATE(YEAR([End Date]),12,31)))

But the catch is with this second measure with no month selection and only year selection it gives me blank values. If no month selection I need the Expense to be shown for all months. 

What changes are supposed to be done?

Would really appreciate help on this..

Thanks in advance!

[MAwwad · Accepted solution]

The reason you are seeing blank values with the Exp Forecast measure when no month is selected is because the DATESBETWEEN function is not receiving any dates to filter by. To show expenses for all months in the selected year when no month is selected, you can modify the Exp Forecast measure as follows:

Exp Forecast = IF(ISFILTERED(Calendar[Month]), CALCULATE(SUM(Table[Sales]), DATESBETWEEN(Calendar[Date], DATE(YEAR([End Date]), MONTH([End Date])+1, 1), DATE(YEAR([End Date]), 12, 31)) ), CALCULATE(SUM(Table[Sales]), DATESBETWEEN(Calendar[Date], DATE(YEAR([End Date]), 1, 1), DATE(YEAR([End Date]), 12, 31)) ) )

This measure uses the ISFILTERED function to check if the Month column is being filtered. If it is, then the original calculation using DATESBETWEEN is used. If it is not being filtered, then the calculation is modified to show expenses for all months in the selected year by changing the start date to January 1st of the selected year.

I hope this helps!