Forum Discussion

Dshami's avatar
Dshami
Frequent Visitor
3 years ago
Solved

Issue with Forecast DAX measure

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 sele...
Need Help
  • MAwwad's avatar
    MAwwad
    3 years ago

    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!

MAwwad's avatar
MAwwad
Solution Sage
3 years ago

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!