Issue with summing months in a quarterly view with DAX

[Anonymous]

Hi all,

I am creating a forecast with two measurements.

Measurement X is a forecast for the year that is developed at the beginning of the year

Measurement Y is real data that is collected throughout the year

My combined Forecast takes the Real Data and combines it with the beginning of the year forecast.

For example, if I have data through the month of May, I use Measurement Y (the real data) for the first five months of the year and then use Measurement X (the forecast) for the remaining months of the yea.

I did this by writing a switch statement where if the Value for the real data was 0, then it returns the forecast.

It works well except for when I am grouping by quarters of the year or the full year. I think this is because when the switch statement is performed at the quarterly level, it will not take the forecast of a month that has not happened.

For an example, I have the following screenshots:

See that in Q2, while the months of April May and June all have data for the grey bar (Measurement Y), only the first two months are summed in the quarterly view.

Any help is greatly appreciated. Thank you.

[Anonymous]

Hi @Anonymous ,

Has this problem been solved? If you have solved it, you can post your solution here. If any of the responses were helpful to you, please consider marking them as solutions, which will help more users find similar problems faster. If the problem is not solved, please feel free to let us know. Thanks in advance!

Best Regards,
Gao
Community Support Team

[Anonymous · Accepted solution]

Hi @Anonymous ,

//See that in Q2, while the months of April May and June all have data for the grey bar (Measurement Y), only the first two months are summed in the quarterly view.

//Measurement Y is real data that is collected throughout the year.

This should be well understood since there is no data for June, the Measurement Y for Q2 will only aggregate data for April and May.

What is your expected output? Is the Measurement Y for the second quarter the sum of the April and May actual data and the June forecast data? If so, create this measure.

MeasureY = 
VAR _table =
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month],
        "Actual",
            VAR _value1 =
                SUM ( 'Table'[Value] )
            VAR _value2 = [MeasureX]
            VAR _result =
                IF ( _value1 = 0, _value2, _value1 )
            RETURN
                _result,
        "Forecast", [MeasureX]
    )
VAR _value = SUMX(_table,[Actual])
RETURN
_value

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data