Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Summarize data from 2 different tables

I have 3 tables as follows:

 

TABLE DATE

DATE
1/1/2020
2/1/2020
3/1/2020
.....
.....

 

TABLE VOLUMES

DATEVOLUMES
1/1/202010
2/1/202015
3/1/202050
......
......

 

TABLE COSTS

DATECOSTVOLUMES YTD (calculated colum)
1/1/20201075
2/1/20201575
3/1/20205075
...... 
...... 

 

They have 1:many relation from table Date to both tables Costs and Volumes.

I need to add a calculated column in table COSTS, named Volumes YTD, containing the sum ytd of volumes for a selected period.

 

Example: if the user wanted to read the volumes ytd from Jan to March, the result would be 75 (in red in the example above) for any row. Besides, I'd like the user to be able to select the ytd period to be considered, meaning that if the user selects February YTD, then the table COST appears as follows:

 

DATECOSTVOLUMES YTD (calculated column)
1/1/20201025
2/1/20201525
3/1/20205025
...... 
...... 

 

Many thanks

Mark

1 Reply

  • As far as I am aware calculated columns are "calculated" when the model is built.

    So the unless the user is rebuilding the model with some kind of parameter table you can't do what you are describing.

     

    Depending on what you are trying to do maybe a measure is a better way to go?