Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Flabenu
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
v-rzhou-msft
Community Support
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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
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.

 

gaurav-lakhotia
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors