Help Needed with DAX Measure for Projecting 2025 Data in Power BI

[Peterjoin1]

Hi everyone,

I'm working on a Power BI report and need some assistance with creating a DAX measure to project data for the year 2025. My dataset includes full-year data for 2023 and 2024, and partial data (3 months) for 2025. I want to calculate the cost per square foot for non-discretionary expenses, ensuring the measure works correctly for all years and projects the full-year value for 2025.

Here's what I'm trying to achieve:

1. Calculate the cost per square foot for non-discretionary expenses:

   • For 2023 and 2024, use the total bill line amount divided by the total square feet.
   • For 2025, project the full-year value by multiplying the partial data by 4 (since I have data for 3 months).

2. Current Measure:

   NonDiscretionaryCostPerSquareFoot = 
   IF(
       MAX('Property Data - Combined'[Year]) = 2025,
       DIVIDE(
           CALCULATE(
               [ProjectedFullYearBillLineAmount],
               'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary"
           ),
           SUM('Property Data - Combined'[Total Square Feet])
       ),
       DIVIDE(
           CALCULATE(
               [TotalBillLineAmount],
               'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary"
           ),
           SUM('Property Data - Combined'[Total Square Feet])
       )
   )

3. Issue:

   • The measure is returning incorrect values for 2023 and 2024.
   • I need help ensuring the measure calculates correctly for all years and projects the full-year value for 2025.

Any guidance or suggestions on how to adjust the DAX formula to achieve this would be greatly appreciated!

Thank you!

[Sahir_Maharaj · Accepted solution]

Hello @Peterjoin1,

Can you please try the following:

NonDiscretionaryCostPerSquareFoot =
VAR CurrentYear = SELECTEDVALUE('Property Data - Combined'[Year])
VAR TotalSqFt =
    CALCULATE(
        SUM('Property Data - Combined'[Total Square Feet])
    )
VAR TotalBillAmount =
    CALCULATE(
        [TotalBillLineAmount],
        'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary"
    )
VAR ProjectedBillAmount2025 =
    CALCULATE(
        [TotalBillLineAmount],
        'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary",
        'Property Data - Combined'[Year] = 2025
    ) * 4

RETURN
IF(
    CurrentYear = 2025,
    DIVIDE(ProjectedBillAmount2025, TotalSqFt),
    DIVIDE(TotalBillAmount, TotalSqFt)
)

[v-pgoloju · Accepted solution]

Hi @Peterjoin1,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[Peterjoin1 · Accepted solution]

Thank you for your responses. I apologize for my delayed reply; I was occupied with a work issue that took up most of the past couple of weeks. The suggestions you provided were accepted by the Power BI system, but the results still did not meet desired expectations. I suspect the issue lies in the raw data I am compiling. I will mark both suggestions as "accepted solutions."

Regarding the report I was building, I have been informed that the individual tab where I was using these equations is no longer needed. In my own time, I will attempt to get the now-hidden tab in the report to work, to satisfy my curiosity. Hopefully, I will identify the issue in my raw data, and one or both of these solutions will yield the expected results.

[v-pgoloju · Accepted solution]

Hi @Peterjoin1,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @Peterjoin1,

Thanks for the update.

Just a gentle reminder,  whenever you get a chance to revisit the issue, if the provided solution ends up working for you, we’d really appreciate it if you could mark it as the Accepted Solution. If you discover a different fix, feel free to share that as well.

Closing the loop helps others in the community who might be facing similar challenges. Thanks again for your time and feedback.

Thank you & regards,
Prasanna Kumar

[v-pgoloju]

Hi @Peterjoin1,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @Peterjoin1,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @Sahir_Maharaj,  for the prompt and helpful response.

We just wanted to follow up and see if there’s anything else you need from our side while you’re reviewing the data. Please feel free to reach out if you need any further assistance.

If super users previous response helped, kindly consider marking it as Accepted as Solution and click Yes if you found it helpful.

Regards,

Prasanna kumar

[Peterjoin1]

Hi Prasanna, really appreciate the follow up. Unfortunately I have been pulled into an issue that has taken all my time and I have not been able to review my Power BI raw data . The code I was given was accepted by the system but the return was not the full amount I was expecting. Since the code is working I suspect its in my data source that is causing the wrong return. I hope to have more time later this week to dig into my data and hopefully the command I was given works and returns the desired results. If it does I'll update the ticket to accept solution

[Peterjoin1]

Thank you Sahir, the equation was accepted but the results were not what I expected. I am going to look at my data set tomorrow and make sure the DAX I have made for combines are accurate. That could be the reason for the incorrect return data. I will report back later this week. 

[Sahir_Maharaj · Accepted solution]

Hello @Peterjoin1,

Can you please try the following:

NonDiscretionaryCostPerSquareFoot =
VAR CurrentYear = SELECTEDVALUE('Property Data - Combined'[Year])
VAR TotalSqFt =
    CALCULATE(
        SUM('Property Data - Combined'[Total Square Feet])
    )
VAR TotalBillAmount =
    CALCULATE(
        [TotalBillLineAmount],
        'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary"
    )
VAR ProjectedBillAmount2025 =
    CALCULATE(
        [TotalBillLineAmount],
        'Property Data - Combined'[Expense Type - CCNH Finance] = "Non Discretionary",
        'Property Data - Combined'[Year] = 2025
    ) * 4

RETURN
IF(
    CurrentYear = 2025,
    DIVIDE(ProjectedBillAmount2025, TotalSqFt),
    DIVIDE(TotalBillAmount, TotalSqFt)
)