Forum Discussion

Microsoft Employee

8 years ago

Solved

Dynamic Measures using different tables

I have two tables. One has the total sales information for each region including current quarter. And this data is day wise. The second table will have the forecasted sales values for each region for...

Need Help

karun_r
8 years ago
Thanks, but I fixed it myself by using some ISFILTERED, MAX and other DAX functions. Had to use some flags as well to get to the final result.

v-ljerr-msft

Microsoft Employee

8 years ago

Hi karun_r,

Could you try using the formula below to see if it works in your scenario? :smileyhappy:

TotalQtrRevenue =
VAR currentQtr =
    CALCULATE ( MAX ( Sales[Fiscal Quarter] ), ALL ( Sales ) )
VAR currentQtrForcastRevenue =
    CALCULATE (
        SUM ( Forecast[Forecast] ),
        FILTER (
            ALL ( Forecast ),
            Forecast[Region] = MAX ( Sales[Region] )
                && Forecast[Fiscal Quarter] = currentQtr
        )
    )
RETURN
    IF (
        MAX ( Sales[Fiscal Quarter] ) = currentQtr,
        currentQtrForcastRevenue,
        [TotalRevenue]
    )

Regards

karun_r

Microsoft Employee

8 years ago

No this still returns the TotalRevenue for the current quarter instead of the Forecast value.