Hi guys,
I am beginner with PowerPivot and need your help/advice to move forward with an issue that I have been struggling with during the last days. Basically I have got in the data model some actual and forecast values for each month. The forecast in the company is updated on a quarterly basis. A slicer selects the months and a pivot table displays the total of actual and forecast values for comparison. If the selection of months is within a single quarter, everything is fine. But the real challenge comes up if the selection contains more that 1 quarter. Due to the fact that the forecast is updated quarterly there is no point to consider ALL the forecast values in the previous month. For the months in the previous quarter(s) actual values should be considered and for months in the actual quarter, forecast values should be consider. That is a form of calculating dynamically a new measure - Dynamic forecast based on the months selected in the slicer. The examples below I believe will make things more clear.
Example 1
Dynamic forecast measure will take the actual numbers for month 2 & 3 + forecast numbers for month 4 & 5 & 6. So the comparison in total will be made 240 vs 270.
Example 2
Dynamic forecast will consider forecast value for month 2&3 as they are within the same quarter.
I believe I need 2 measures:
1) to determine if the months in the filtering 'belongs' to previous quarter(s) or actual quater
2) to calculate the dynamic forecast using the previous measure
But I really have no idea how to write the DAX formula for that. Please help me with any reference/advice. I have researched several forums/blogs to see similar situations but with no success.
Thank you very much for your time and effort.
Florian
I am beginner with PowerPivot and need your help/advice to move forward with an issue that I have been struggling with during the last days. Basically I have got in the data model some actual and forecast values for each month. The forecast in the company is updated on a quarterly basis. A slicer selects the months and a pivot table displays the total of actual and forecast values for comparison. If the selection of months is within a single quarter, everything is fine. But the real challenge comes up if the selection contains more that 1 quarter. Due to the fact that the forecast is updated quarterly there is no point to consider ALL the forecast values in the previous month. For the months in the previous quarter(s) actual values should be considered and for months in the actual quarter, forecast values should be consider. That is a form of calculating dynamically a new measure - Dynamic forecast based on the months selected in the slicer. The examples below I believe will make things more clear.
Example 1
Dynamic forecast measure will take the actual numbers for month 2 & 3 + forecast numbers for month 4 & 5 & 6. So the comparison in total will be made 240 vs 270.
Example 2
Dynamic forecast will consider forecast value for month 2&3 as they are within the same quarter.
I believe I need 2 measures:
1) to determine if the months in the filtering 'belongs' to previous quarter(s) or actual quater
2) to calculate the dynamic forecast using the previous measure
But I really have no idea how to write the DAX formula for that. Please help me with any reference/advice. I have researched several forums/blogs to see similar situations but with no success.
Thank you very much for your time and effort.
Florian
