Same formula acting different in two reports

[Gugge]

Hi,

I'm stuck with a problem I cant solve. I have two reports used for sales reporting for various reasons. The data I'm extracting from NAV is from the same server. The data where I have the issue is extracting a table in NAV that's summarizing Orders and Territory on a daily basis. In PowerBI I'm consolidating this data with other sales data to build a complete revenue table. Both reports has the same formula to calculate difference between two rows based on date value and territory, for example the value today would be Xterritory TodayOrder minus XterritoryYesterday orders. There are a few gaps in the dates where the NAV report hasn't been ran, which causes an issue.

I'm using the formulas below. In my WorldSales report, the formula works as supposed even when there is a gap between the dates. In my US Sales report it only works when the date is the previous date. I've extracted some examples below to show the error, in world sales I'm getting sales numbers on all rows except the first one (which is ok), but in US I'm not getting data when the gap is more than two days. It is working between May 21 and May 19, but not May 19 and May 12.

Is there a setting somewhere I'm missing on how dates work, or where do I go wrong?

_____World sales_____
ChangeOpenOrders =
VAR __date = 'NAV_SalesDataConsolidated'[posting_Date]
VAR __filterTable = ALLEXCEPT( 'NAV_SalesDataConsolidated', 'NAV_SalesDataConsolidated'[TerritoryCode])
VAR __previousDate =
CALCULATE(
MAX( 'NAV_SalesDataConsolidated'[posting_Date] ),
__filterTable,
'NAV_SalesDataConsolidated'[Posting_Date] < __date
)
VAR __previousAmt =
CALCULATE(
SUM('NAV_SalesDataConsolidated'[Total_Open_Orders_$] ),
__filterTable,
'NAV_SalesDataConsolidated'[Posting_Date] = __previousDate
)
RETURN IF( NOT ISBLANK( __previousAmt ), 'NAV_SalesDataConsolidated'[Total_Open_Orders_$] - __previousAmt )

_____US SALES_____
ChangeOpenOrders =
VAR __date = 'TotalSales'[posting_Date]
VAR __filterTable = ALLEXCEPT( 'TotalSales', 'TotalSales'[INVCTerritoryCode])
VAR __previousDate =
CALCULATE(
MAX( 'TotalSales'[posting_Date] ),
__filterTable,
'TotalSales'[Posting_Date] < __date
)
VAR __previousAmt =
CALCULATE(
SUM('TotalSales'[Total_Open_Orders] ),
__filterTable,
'TotalSales'[Posting_Date] = __previousDate
)
RETURN IF( NOT ISBLANK( __previousAmt ), 'TotalSales'[Total_Open_Orders] - __previousAmt )

Worlds sales data (I removed dates between 2021 and 2023)

Posting_Date	Description	Product	Sales_USD	Total_Open_Orders_$	ChangeOpenOrders	TerritoryCode
2020-12-31	OpenOrders	OpenOrders		258 688		XYZ
2021-03-31	OpenOrders	OpenOrders	36 313	295 000	36 313	XYZ
2021-05-31	OpenOrders	OpenOrders	-164 406	130 594	-164 406	XYZ
2021-06-16	OpenOrders	OpenOrders	234 700	365 294	234 700	XYZ
2021-06-17	OpenOrders	OpenOrders	75 950	441 244	75 950	XYZ
2021-06-18	OpenOrders	OpenOrders	-60 000	381 244	-60 000	XYZ
2023-05-12	OpenOrders	OpenOrders	59 188	403 375	59 188	XYZ
2023-05-19	OpenOrders	OpenOrders	52 850	456 225	52 850	XYZ
2023-05-21	OpenOrders	OpenOrders	32 500	488 725	32 500	XYZ

US Sales (I removed dates between 2021 and 2023)

No	Description	Posting_Date	INVCTerritoryCode	ChangeOpenOrders	Total_Open_Orders	SalesAmount
OpenOrders	OpenOrders	2020-12-31	XYZ		258 688
OpenOrders	OpenOrders	2021-03-31	XYZ		295 000
OpenOrders	OpenOrders	2021-05-31	XYZ		130 594
OpenOrders	OpenOrders	2021-06-16	XYZ		365 294
OpenOrders	OpenOrders	2021-06-17	XYZ	75 950	441 244	75 950
OpenOrders	OpenOrders	2021-06-18	XYZ	-60 000	381 244	-60 000
OpenOrders	OpenOrders	2023-05-12	XYZ	59 188	403 375	59 188
OpenOrders	OpenOrders	2023-05-19	XYZ		456 225
OpenOrders	OpenOrders	2023-05-21	XYZ	32 500	488 725	32 500

[Idrissshatila]

Hello @Gugge ,

check if there's any filter that is affecting the report or the chart.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Gugge]

Hi,

This is a column I've added to the sales table to calculate the change, so there are no filters on the report or chart that should effect the calculation.