Measure gives wrong numbers

[Anonymous]

Hi guys,

I am trying to make some sort of planning: The hours worked and the planned hours should be combined into one running total. For the most part it works just fine, until I get to the cumulative total. (and the total of Test_Total).

Maandnummer: number of the different months.

Hours: Hours that were planned in advance.

Quantity: Actually worked Hours.

Test_Total: a measure to decide wether I need Hours or Quantity (based on current month)

Test_Total = IF(DATEDIFF(MAX(TimeTransactions[Date]), NOW(), DAY) > 0, SUM(TimeTransactions[Quantity]), IF(SUM(Forecast[Hours]) = BLANK(), 0, SUM(Forecast[Hours])))

Test_FinalTotal =  The cumulative total of Test_Total

Test_FinalTotal = CALCULATE(Test[Test_Total],FILTER(ALL(PowerBI_Calendar[Maandnummer]), PowerBI_Calendar[Maandnummer] <= MAX(PowerBI_Calendar[Maandnummer])))

Does anyone know what is going wrong?

[v-lili6-msft · Accepted solution]

hi @Anonymous 

Just adjust the formula as below:

Test_FinalTotal 3 = SUMX(FILTER(ALL(PowerBI_Calendar[Maandnummer]), PowerBI_Calendar[Maandnummer] <= MAX(PowerBI_Calendar[Maandnummer])),[Test_Total])

or

Test_FinalTotal 2 = SUMX(FILTER(ALL(PowerBI_Calendar), PowerBI_Calendar[Date] <= MAX(PowerBI_Calendar[Date])),[Test_Total])

Result:

Regards,

Lin

[az38]

Hi @Anonymous 

I see your problem.

it looks like you have too much tables here 🙂

check relationships between PowerBI_Calendar and Forecast tables as problem appears when you need to sum up Forecast[Hours]

[amitchandak]

@Anonymous , what is wrong and what is expected output

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.