Calculate amount depending of other table and column

[cristianml]

Hi,

I need a Dax measure to calculate a difficult formula. I have 2 tables : Ad hoc table and Forecast table. Each table has a column called "Actual or Forecast" which has two possible texts: "Actual" or "Forecast".

Note: these 2 tables are already related/have a relationship by period and other categories.

What I need is a dax formula that follows this logic: Sum Amount of Ad hoc table + Amount of Forecast table till the column "Actual or Forecast" from Forecast table changes to "Forecast". In that moment I need to sum the Ad hoc amounts till the column "Actual or Forecast" from Forecast table changes to "Forecast" + the amounts of the forecast table. 

To understand and visualize this logic better I can show you this:

ad hoc Table

Sep-18

Oct-18

Nov-18

Dec-18

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Depreciation

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

4,200.00

-

-

Forecast Table
	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19
	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast
Depreciation									4,000.00	4,000.00	4,000.00	4,000.00
Other Forecast costs	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00
Total	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	1,000.00	5,000.00	5,000.00	5,000.00	5,000.00

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Forecast

Forecast

Forecast

Forecast

Sep-18

Oct-18

Nov-18

Dec-18

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Result Forecast  table + ad hoc table

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

9,200.00

9,200.00

5,000.00

5,000.00

issue here

issue here

This is the correct result that it should be (Total)

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Actual

Forecast

Forecast

Forecast

Forecast

Sep-18

Oct-18

Nov-18

Dec-18

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,200.00

5,000.00

5,000.00

5,000.00

5,000.00

Correct

Correct

Thanks.

[v-juanli-msft]

Hi @cristianml 

Create a calendar table

calendar = ADDCOLUMNS(CALENDARAUTO(),"month-year",FORMAT([Date],"mmm-yy"),"monthnumber",FORMAT([Date],"yyyymm"))

Create relationship based on "date" column

Create measures

hoc_value = SUM(hoc[value])

forecast_value = SUM(forecast[value])

forecast_other_value = SUM(forecast[other value])

sum_forecast = [forecast_value]+[forecast_other_value]

final_value = IF(MAX(forecast[category])="Forecast",[sum_forecast],[hoc_value]+[sum_forecast])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cristianml]

Hi @v-juanli-msft ,

There is a value missed. The table Forecast also have "Actual" amounts that need to be included. Also I don't need to create a calendar cause my model already has this (see the  picture below). Follow the tables below for more visibility and use them as base to work on the result measure that I need (below):

Ad hoc table
Amount	Actual or Forecast	Date	Category
4,200.00	Actual	2018-09	Depreciation
4,200.00	Actual	2018-10	Depreciation
4,200.00	Actual	2018-11	Depreciation
4,200.00	Actual	2018-12	Depreciation
4,200.00	Actual	2019-01	Depreciation
4,200.00	Actual	2019-02	Depreciation
4,200.00	Actual	2019-03	Depreciation
4,200.00	Actual	2019-04	Depreciation
4,200.00	Actual	2019-05	Depreciation
4,200.00	Actual	2019-06	Depreciation
-		2019-07	Depreciation
-		2019-08	Depreciation

Forecast Table:

Actual or Forecast	Amount	Category	Date
Actual	200.00	Technology Charges	2018-09
Actual	800.00	Seat Charges	2018-09
Actual	1,000.00	Per diem	2018-10
Actual	1,000.00	Other business	2018-11
Actual	700.00	Travel	2018-12
Actual	300.00	Office Supplies	2018-12
Actual	400.00	Training	2019-01
Actual	600.00	Seat Charges	2019-01
Actual	1,000.00	Facilities	2019-02
Actual	500.00	Office Supplies	2019-03
Actual	500.00	Training	2019-03
Actual	1,000.00	Other costs	2019-04
Forecast	350.00	Travel	2019-05
Forecast	650.00	Per diem	2019-05
Forecast	4,000.00	Depreciation	2019-05
Forecast	350.00	Travel	2019-06
Forecast	650.00	Per diem	2019-06
Forecast	4,000.00	Depreciation	2019-06
Forecast	500.00	Travel	2019-07
Forecast	500.00	Per diem	2019-07
Forecast	4,000.00	Depreciation	2019-07
Forecast	500.00	Travel	2019-08
Forecast	500.00	Per diem	2019-08
Forecast	4,000.00	Depreciation	2019-08

Date	2018-09	2018-10	2018-11	2018-12	2019-01	2019-02	2019-03	2019-04	2019-05	2019-06	2019-07	2019-08
Actual or forecast	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Forecast	Forecast
Result (measure)	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,200.00	5,000.00	5,000.00

Screenhoot of my real model:

Thanks,