cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Actuals and Forecast in a Matrix

Hi -

Trying to come up with a measure syntax to display Actuals and Forecast number in a matrix. Please assist with the correct DAX measure syntax. Currently, it's showing "\$0" amounts for future months.

I'm using this:

```Actuals Forecast:=

if(
VALUES('Calendar'[Month]) <= Month(Today()),
[Labor Actuals],
[Actuals/Forecast]

)```

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Actuals and Forecast in a Matrix

Hi @miii ,

You need to redo your measure to something like this:

```Actuals Forecast :=
IF (
HASONEVALUE ( 'Calendar'[Month] );
IF ( [Group Actuals Non-Blank] = 0; [Indirect Forecast]; [Indirect Actual] );
SUMX (
Calendar;
IF ( [Group Actuals Non-Blank] = 0; [Indirect Forecast]; [Indirect Actual] )
)
)```

Should work.

Regards,

MFelix

Proud to be a Datanaut!

8 REPLIES 8
Super User

## Re: Actuals and Forecast in a Matrix

Hi @miii ,

Without further details is difficult to give you a correct answer, butare tha Actuals and Actuals/forecast on different tables?

Do those tables have a relationship with the Calendar Table?

Can you share a sample of the data?

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: Actuals and Forecast in a Matrix

Hi MFelix,

Yes -

Table1: Forecast Table

Date -> joins to calendar table on date

Division -> joins to a division bridge table

Indirect Labor Forecast -> Measure of forecast by Month

Table 2: Actuals

Date -> joins to calendar table on date

Division -> joins to a division bridge table

Indirect Labor Actuals -> Measure of actuals by Month

And here is a sample:

Super User

## Re: Actuals and Forecast in a Matrix

Hi @miii ,

You need to change your measure to:

```Actuals Forecast =
IF (
SELECTEDVALUE ( 'Calendar'[Month] ) <= MONTH ( TODAY () ),
[Labor Actuals],
[Actuals/Forecast]
)```

should work fine

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: Actuals and Forecast in a Matrix

Unfortunately, "SELECTEDVALUE' syntax is not available in PowerBI version we are using.

Regular Visitor

## Re: Actuals and Forecast in a Matrix

Hello, I would suggest creating a Calculated column in the Query with the similar If Condiiton, so you can have a defined column for each, then you can eaither pivot them to see them as attribute or add the columns themselves.

Frequent Visitor

## Re: Actuals and Forecast in a Matrix

I used the following and it's displaying the monthly forecast now but missing the total amount in the grid. It only returns January number in the total.

```Actuals Forecast:=

If ([Group Actuals Non-Blank] = 0,
[Indirect Forecast],[Indirect Actual]
)```

Super User

## Re: Actuals and Forecast in a Matrix

Hi @miii ,

You need to redo your measure to something like this:

```Actuals Forecast :=
IF (
HASONEVALUE ( 'Calendar'[Month] );
IF ( [Group Actuals Non-Blank] = 0; [Indirect Forecast]; [Indirect Actual] );
SUMX (
Calendar;
IF ( [Group Actuals Non-Blank] = 0; [Indirect Forecast]; [Indirect Actual] )
)
)```

Should work.

Regards,

MFelix