Forum Discussion
Inaccurate data with calculate function
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.
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-yingjlCommunity Support
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 RevenueIf 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.