Inaccurate data with calculate function

[thenerv25]

I have a calculated column (YTD Revenue) that is displaying accurate totals (when compared to the source file), but the line level detail (when displaying with another attribute in the same table) is significantly off. The Revenue column in the belowsnippet is just the raw revenue column from the fact table and it matches what is in the source data (at the line level and in total). Customer number is not a dim, it is just an attribute on that same fact table that has the raw revenue field. I've included my YTD revenue calculation below the image. 

When i export this table and do a sum in excel, the PA YTD revenue column actually sums up to 638k (more than 10% above the accurate total of 561k), yet it displays here as 561k. You can see some of the rows that are different in the image below. I have filtered the data just on this most recent month to simplify the initial analysis. So while all months are similarly off, this is obviously just showing October.

Has anyone seen this behavior with the calculate columns and/or figured out a solution for the root cause? Thank you so much for the help, this has been a big challenge for weeks and is preventing us from being able to drill down accurately!

PA YTD Revenue =

var MonthsToDate = Month([PA Max Invoice Date])

var currentYear = Year([PA Max Invoice Date] )

//Max invoice date will always be the end of the most recently loaded month

var Revenue =

CALCULATE(

[PA Total Revenue]

,'DIM Date'[Month of Year] <= MonthsToDate

,'DIM Date'[Year] = currentYear

)

return Revenue

[thenerv25 · Accepted solution]

@v-yingjl thank you for taking a stab at this. Unfortunately Power BI wouldn't take the syntax of .Month or .Year.

The good news is that I have figured this out. In case anyone else ever runs into an issue like this, below is the summary.

I had understood max(invoice date) to be taking the maximum invoice date of the full data set and applying that 1 date value to all records in a table that would be displaying that measure. What I realized is that the max(invoice date) is actually separately evaluating each row/record in the table (i.e. taking the maximum invoice date for each row) which yields very different results, and tables where the totals (the only place that actually does use the full data set value) will not match the sum of the table. 

[v-yingjl]

Hi @thenerv25 ,

Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.

Best Regards,
Community Support Team _ Yingjie Li

[thenerv25]

I've done some further troubleshooting on this and have narrowed it down to something very odd that I thought I would throw out there in case it triggered something for someone. 

When looking at the formula below, if i hard code the month (e.g. 10), it works perfectly fine. When I leave it dynamically pulled, i get the issue described above. I have quadruple checked that the dynamic value (month([PA Max Invoice Date]) is as expected and also confirmed that hard coded and dynamically pulled have the same data type (20). Any tips or clues as to what might be going on would be greatly appreciated!

[v-yingjl]

Hi @thenerv25 ,

Try to modify the formula like this:

PA YTD Revenue =
VAR MonthsToDate = [PA Max Invoice Date].MONTH
VAR currentYear = [PA Max Invoice Date].YEAR //Max invoice date will always be the end of the most recently loaded month
VAR Revenue =
    CALCULATE (
        [PA Total Revenue],
        'DIM Date'[Month of Year] <= MonthsToDate,
        'DIM Date'[Year] = currentYear
    )
RETURN
    Revenue

If not work, could you please consider sharing a sample file without sesentive information for further discussion?

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.