DAX for a powerpivot model

[Ackbar-Learner]

Hi i actually had put a previous post but got no positive replies, so I have tried to narrow down where the issue is coming and re-issue same in a new post.

So this is a balance sheet that i made using power pivot DAX

As you can see Total Assets is not equal to Total Equity & Liabilities as the line "Cash in hand / at bank" is blank. Total Assets and Total Equity & Liabilities are calculated as DAX running totals and it is working fine.

Another important table is below which defines how the balance sheet works:

Below are 3 consecutive DAX measures to firstly calculate the bank balance, then secondly insert that amount as well as some other amounts inside the Balance Sheet and thirdly to summarize the data inserted:

Step1:

42bBMBSBankBalance:=VAR EndDate = max(BudgetCalendar[Date])
VAR Result = SUMX(SUMMARIZE (TabBSItem,TabBSItem[BSItem]), (CALCULATE([01BMBudgetDataAllLines],all(TabBSItem[BSItem]),all(BudgetCalendar[Date]),DATESBETWEEN(BudgetCalendar[Date],[38BMBS01aAlustreStartDateFirstYear],EndDate),BudgetInputsDatabase[Dimension3]<>"")+if([41BMBS03aNetProfitEURAllYearsTax]<0,0,[41BMBS03aNetProfitEURAllYearsTax]))*-1)
RETURN Result

Step2:

43bBMBS04AsAtAllTransactionAmountwithResultsYTD:=IF(IF(HASONEVALUE(TabBSItem[BSItem]),values(TabBSItem[BSItem]))="Results for the period",[41BMBS03aNetProfitEURCurrentYearAfterTax],IF(IF(HASONEVALUE(TabBSItem[BSItem]),values(TabBSItem[BSItem]))="Results b/f",[42BMBS03bNetProfitEURPreviousYearsAfterTax],IF(IF(HASONEVALUE(TabBSItem[BSItem]),values(TabBSItem[BSItem]))="Cash in hand / at bank",[42bBMBSBankBalance],[40BMBS02aAsAtAllTransactionAmountEUR])))

Step3:

44bBMBS05AllTransactionAmountwithSign:=SUMX(SUMMARIZE (TabBSItem,TabBSItem[BSItem]), CALCULATE( VAR result = [43bBMBS04AsAtAllTransactionAmountwithResultsYTD]*[37BMBSSignMeasure]
        RETURN
            result))

When i use these steps and after a few more measures, see below end result where the running totals for Total Assets and Total Equity & Liabilities are wrong:

One could say that maybe the measures in between are wrong but they are all actually correct. I did a test by replacing the first measure above by the amount directly and the results turn out right:

42bBMBSBankBalance:=6111918

I think the problem lies with summarizing the data but i cannot seem to address the issue.

Any help is most welcome, thanks 🙂

[123abc]

The issue you're facing with your Power Pivot model and DAX measures not producing the correct results could be due to various factors, including the complexity of the calculations, the relationships between tables, and the context in which the measures are used. Let's try to troubleshoot and optimize your DAX measures:

1. Step 1 - 42bBMBSBankBalance:

   • Ensure that your BudgetCalendar table has the necessary date hierarchy and contains the expected data for calculations.
   • Check if the relationships between tables are correctly established. Make sure there are no filtering issues.

2. Step 2 - 43bBMBS04AsAtAllTransactionAmountwithResultsYTD:

   • This measure seems to use conditional logic to determine the value to be displayed based on the selected item. Ensure that the item you're selecting ("Cash in hand / at bank") is correctly mapped to this measure.
   • Check if there are any conflicts between this measure and other measures. Make sure there are no circular dependencies.

3. Step 3 - 44bBMBS05AllTransactionAmountwithSign:

   • This measure appears to calculate a signed amount based on a sign measure. Ensure that the sign measure [37BMBSSignMeasure] is behaving as expected.
   • Check if the measure [43bBMBS04AsAtAllTransactionAmountwithResultsYTD] is correctly returning the values you expect.

4. Context and Relationships:

   • Ensure that the relationships between tables are correctly set up, especially those between the date table and other relevant tables.
   • Pay attention to context transitions in DAX calculations. Measure results can be influenced by row context and filter context.

5. Debugging:

   • To isolate the issue, try using DAX measures to display intermediate results at each step. For example, you can create a measure to display the output of Step 1, Step 2, and Step 3 individually.
   • Use the DAX function EVALUATE in DAX Studio or Power BI to inspect the intermediate results of your measures. This can help you identify where the issue is occurring.

6. Optimization:

   • Consider optimizing your measures for performance. Complex measures can sometimes result in slower query performance. You can use techniques like EARLIER, FILTER, and SUMX optimization to improve performance.

7. Data Validation:

   • Double-check your data to ensure that there are no outliers or unusual data points that could be affecting your calculations.

By carefully reviewing and debugging each step of your DAX measures and ensuring that your data and relationships are correctly set up, you should be able to identify and resolve the issue that is causing the incorrect results in your Power Pivot model.

[Ackbar-Learner]

Hi @123abc 

If you see below picture which is already in post above, this is Step 1, 2, 3 and the final measure (there are some measures in between)

I replaced the first measure by the actual figure and you can see in below picture, everything works fine.

So i was hoping someone might be able to see a mistake in the DAX measures provided above 🙂