Forum Discussion

awitt1Temp's avatar
awitt1Temp
Frequent Visitor
3 years ago
Solved

Retrieve data in measure from different tables based on date

I am looking to write a single measure that adds a sales + forecast value for a give month. To do this I have to sum data in a sales table for dates today and earlier with the forecasted amount for f...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    awitt1Temp,

     

    Try this solution.

     

    1. Create a date table with relationships to each table:

     

     

    2. Create measures:

     

    Actual Sales = SUM ( Actual[Actual Sales] )
    Forecast = SUM ( Forecast[Forecast] )
    Blended Amount = 
VAR vActualEndDate =
    CALCULATE ( MAX ( Actual[Date] ), ALL ( 'Date' ) )
VAR vForecast =
    CALCULATE ( [Forecast], KEEPFILTERS ( 'Date'[Date] > vActualEndDate ) )
VAR vResult = [Actual Sales] + vForecast
RETURN
    vResult

     

    3. In a table visual, add Date (must be from Date table) and [Blended Amount]:

     

     

DataInsights's avatar
DataInsights
Super User
3 years ago

awitt1Temp,

 

Try this solution.

 

1. Create a date table with relationships to each table:

 

 

2. Create measures:

 

Actual Sales = SUM ( Actual[Actual Sales] )
Forecast = SUM ( Forecast[Forecast] )
Blended Amount = 
VAR vActualEndDate =
    CALCULATE ( MAX ( Actual[Date] ), ALL ( 'Date' ) )
VAR vForecast =
    CALCULATE ( [Forecast], KEEPFILTERS ( 'Date'[Date] > vActualEndDate ) )
VAR vResult = [Actual Sales] + vForecast
RETURN
    vResult

 

3. In a table visual, add Date (must be from Date table) and [Blended Amount]: