Need DAX Measure Correction

[Khalidshurafa]

Dears,
I need help with correcting the below DAX measure in which I target measuing progress from latest available date in the data report as it includes data from several dates

please note that it only works well for the second argument but there is still an issue with the first and third ones,

Overall Actual Progress =

 VAR LatestDate = CALCULATE(MAX('Project Calendar'[Date]), ALL('Weekly Progress'[Reporitng Date]))

 VAR SingleBuildingProgress = SELECTEDVALUE(SubAssets[BuildingName])

 VAR EntireAssetProgress = SELECTEDVALUE(SubAssets[Asset Name])

 VAR SelectedAssetBUA = SELECTEDVALUE('Weekly Progress'[Building BUA])

 VAR TotalBUAForAsset = CALCULATE(SUM('Weekly Progress'[Building BUA]), SubAssets[Asset Name] = EntireAssetProgress, 'Weekly Progress'[Reporitng Date] = LatestDate)

 RETURN

 IF(

    ISBLANK(EntireAssetProgress),

    SUMX(

        FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

        'Weekly Progress'[Actual Progress] * 'Weekly Progress'[Building BUA] / SUMX(

            FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

            'Weekly Progress'[Building BUA]

        )

    ),

    IF(

        NOT(ISBLANK(SingleBuildingProgress)),

        LOOKUPVALUE(

            'Weekly Progress'[Actual Progress],

            'Weekly Progress'[BuildingName], SingleBuildingProgress, 'Weekly Progress'[Reporitng Date], LatestDate

        ),

        CALCULATE(

            SUMX(

                FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

                'Weekly Progress'[Building BUA] * 'Weekly Progress'[Actual Progress] * DIVIDE(

                    1,

                    TotalBUAForAsset

                )

            ),

            SubAssets[Asset Name] = EntireAssetProgress

        )

    )

)

[v-zhengdxu-msft]

Hi @Khalidshurafa 

Please share more details:

What is the relationship between your tables?

Which visual you're using?

What is the second argument, if possible, please share some screenshot to explain what issue you've met and your target outcome.

Please share some raw data(Exclude sensitive data) or create some sample data, so that we can help you better.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

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

[Khalidshurafa · Accepted solution]

Hi @v-zhengdxu-msft 
I actually was able to fix it myself after deep analysis of the measure,

I actually had one reference inside the varibel called LatestDate to another table in the data model which wasnt supposed to be there,

Below is the correct one,

Overall Actual Progress =

 VAR LatestDate = CALCULATE(MAX('Weekly Progress'[Reporitng Date]), ALL('Weekly Progress'[Reporitng Date]))

 VAR SingleBuildingProgress = SELECTEDVALUE(SubAssets[BuildingName])

 VAR EntireAssetProgress = SELECTEDVALUE(SubAssets[Asset Name])

 VAR SelectedAssetBUA = SELECTEDVALUE('Weekly Progress'[Building BUA])

 VAR TotalBUAForAsset = CALCULATE(SUM('Weekly Progress'[Building BUA]), SubAssets[Asset Name] = EntireAssetProgress, 'Weekly Progress'[Reporitng Date] = LatestDate)

 RETURN

 IF(

    ISBLANK(EntireAssetProgress),

    SUMX(

        FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

        'Weekly Progress'[Actual Progress] * 'Weekly Progress'[Building BUA] / SUMX(

            FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

            'Weekly Progress'[Building BUA]

        )

    ),

    IF(

        NOT(ISBLANK(SingleBuildingProgress)),

        LOOKUPVALUE(

            'Weekly Progress'[Actual Progress],

            'Weekly Progress'[BuildingName], SingleBuildingProgress, 'Weekly Progress'[Reporitng Date], LatestDate

        ),

        CALCULATE(

            SUMX(

                FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),

                'Weekly Progress'[Building BUA] * 'Weekly Progress'[Actual Progress] * DIVIDE(

                    1,

                    TotalBUAForAsset

                )

            ),

            SubAssets[Asset Name] = EntireAssetProgress

        )

    )

)

Thanks mate,