DAX Help

[Anonymous]

I am creating a finance report that picks up a number of data fields for our Finance people.

I have a column for FY24 Budget Total, which is fed from MS Project, from this I need to calculate the project Estimate at Completion, for which I created the following Measure

FY24EstimateAtCompletion = [FY24CostToDate]+[FY24CTAToDate]+

            CALCULATE(

                SUM(Tasks[TaskRemainingCost]),

                    FILTER(

                        Tasks,

                       Tasks[TaskIsSummary]=FALSE() &&

                        Tasks[TaskFinishDate]>DATE(2023,03,31)))

                        +0

Where

FY24CostToDate is a Measure, picking up data from the Finance supplied Excel data table, filtered to capture only data from Apr-23 onwards

FY24CostToDate = CALCULATE(

                    Sum('_Finance Raw Data'[Val/COArea Crcy]),

                        FILTER(

                            '_Finance Raw Data',

                            '_Finance Raw Data'[Period]>=DATE(2023,04,01)

                        )

)

FY24CTAToDate is a measure taken from the Task table from MS Project, this data only started being collected in Apr-24, hence no date filter

FY24CTAToDate = CALCULATE(

                    Sum('Tasks'[CTACost])

)

TaskRemainingCost is fed directly from MS Project Tasks table and is filtered to take only tasks that end after 31st March 2023 and are not MS Project summary tasks

All the above work perfectly and the figures all agree with what Finance expect.

I am now trying to create a variance figure, comparing the FY24EstimateAtCompletion against the FY24BudgetTotal, however the figures go wildly out with the DAX I tried to create, which was simply a merge of the code above, but I don't know what to change

FY24Variance = Projects[FY24BudgetTotal] -

            ([FY24CostToDate]+[FY24CTAToDate]+

                CALCULATE(

                    SUM(Tasks[TaskRemainingCost]),

                        FILTER(

                                Tasks,

                                Tasks[TaskIsSummary]=FALSE() &&

                                Tasks[TaskFinishDate]>DATE(2023,03,31))))

Finally, I need to create a variance %'age figure, which I know needs to be the result of the Variance calculation divided by the Budget total, again, this goes wild, up into the hundreds of %.

Any help gratefully received

Fred

[Anonymous · Accepted solution]

Closed - Found the cause, I was using a Column whereas it should have been a Measure

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.