cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

## Calculated Table for progressive costs

I have three tables, PROJECTS, TIME and ACTUAL. In PROJECTS I have the list of projects (PROJECTID, DESCRIPTION), in TIME I have the list of all the dates (DATE, YEAR, MONTH, YEAR-MONTH), in ACTUAL there are the costs recorded for each project and for each date (PROJECTID , DATE, YEAR, MONTH, YEAR-MONTH, ARTICLE, QUANTITY, AMOUNT). The ACTUAL table is logically linked with a 1:N relationship to the PROJECTS table (PROJECTS.PROJECTID 1:N ACTUAL.PROJECTID). The ACTUAL table is also logically linked to the TIME table (TIME.DATE 1:N ACTUAL.DATE). The PROJECTS and TIME tables are not logically linked. How could the DAX code be to build a calculated table that has as fields: PROJECTID, YEAR-MONTH and COSTITOT (where COSTITOT is for PROJECT/YEAR-MONTH the cumulative cost, i.e. the progressive sum of all the ACTUAL.AMOUNT from the beginning of dates up to YEAR-MONTH ) ?

1 ACCEPTED SOLUTION
Community Support

Hi @Flabenu ,

According to your statement, I think [PROJECTID] and [YEAR-MONTH] you need are both in 'ACTUAL' table.

You can just create the calculated table based on 'ACTUAL' table.

I am still confused about the calculation logic about [COSTITOT] which is based on PROJECT/YEAR-MONTH.

Do you want to get a table which will show progressive costs? I think it should be comulative amount/ comulative quantity.

I think you can try code as below to create a calculated table.

``````Calculated Table =
SUMMARIZE (
'ACTUAL',
'ACTUAL'[PROJECTID],
'ACTUAL'[YEAR-MONTH],
"Progressive Costs",
VAR _Quantity =
CALCULATE (
SUM ( 'ACTUAL'[QUANTITY] ),
FILTER (
'ACTUAL',
'ACTUAL'[PROJECTID] = EARLIER ( [PROJECTID] )
&& 'ACTUAL'[YEAR-MONTH] <= EARLIER ( [YEAR-MONTH] )
)
)
VAR _AMOUNT =
CALCULATE (
SUM ( 'ACTUAL'[AMOUNT] ),
FILTER (
'ACTUAL',
'ACTUAL'[PROJECTID] = EARLIER ( [PROJECTID] )
&& 'ACTUAL'[YEAR-MONTH] <= EARLIER ( [YEAR-MONTH] )
)
)
RETURN
DIVIDE ( _AMOUNT, _Quantity )
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @Flabenu ,

According to your statement, I think [PROJECTID] and [YEAR-MONTH] you need are both in 'ACTUAL' table.

You can just create the calculated table based on 'ACTUAL' table.

I am still confused about the calculation logic about [COSTITOT] which is based on PROJECT/YEAR-MONTH.

Do you want to get a table which will show progressive costs? I think it should be comulative amount/ comulative quantity.

I think you can try code as below to create a calculated table.

``````Calculated Table =
SUMMARIZE (
'ACTUAL',
'ACTUAL'[PROJECTID],
'ACTUAL'[YEAR-MONTH],
"Progressive Costs",
VAR _Quantity =
CALCULATE (
SUM ( 'ACTUAL'[QUANTITY] ),
FILTER (
'ACTUAL',
'ACTUAL'[PROJECTID] = EARLIER ( [PROJECTID] )
&& 'ACTUAL'[YEAR-MONTH] <= EARLIER ( [YEAR-MONTH] )
)
)
VAR _AMOUNT =
CALCULATE (
SUM ( 'ACTUAL'[AMOUNT] ),
FILTER (
'ACTUAL',
'ACTUAL'[PROJECTID] = EARLIER ( [PROJECTID] )
&& 'ACTUAL'[YEAR-MONTH] <= EARLIER ( [YEAR-MONTH] )
)
)
RETURN
DIVIDE ( _AMOUNT, _Quantity )
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Hi @Flabenu ,

Make the relationship between TIME & ACTUAL bi-directional & then try to create the table. If it doesn't work, please share the sample file.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors