Hi Power BI community,
I wrote some queries which I believe were working correctly last year but in the new year these do not seem to function as intended. There is a calendar table I created over the course of last year as well as using the DocDate from tables.
To explain the premise I am wanting to show total sales year to date (YTD) minus any credits year to date.
I am then using 2 additional queries to calculate the same for the previous year to date and then comparing the 2 to see % increase/decrease.
These queries seemed to be working okay last year but after rolling over into the new year I am now not so sure.
My queries as follows:
TotalYTD-Credits = [TotalYTDexcVAT] - [TotalCreditsYTDExcVAT]
TotalYTDexcVAT = TOTALYTD(CALCULATE(SUM(OINV[DocTotal]) - SUM(OINV[VatSum])), OINV[DocDate])
TotalCreditsYTDExcVAT = TOTALYTD(
CALCULATE(SUM(ORIN[DocTotal]) - SUM(ORIN[VatSum])),
ORIN[DocDate]
)
TotalPYTD-Credits = [TotalPYTDexcVAT] - [TotalCreditsPYTDexcVAT]
TotalPYTDexcVAT = TOTALYTD(
CALCULATE(SUM(OINV[DocTotal]) - SUM(OINV[VatSum])),
SAMEPERIODLASTYEAR('Calendar Table'[Date])
)
TotalCreditsPYTDexcVAT = TOTALYTD(
CALCULATE(SUM(ORIN[DocTotal]) - SUM(ORIN[VatSum])),
SAMEPERIODLASTYEAR(ORIN[DocDate])
)
To calculate the difference my query is:
TotalPrYR%-Credits = ([TotalYTD-Credits] - [TotalPYTD-Credits]) / [TotalPYTD-Credits]
The calendar table creation code as follows:
Calendar Table =
VAR MinDate = YEAR ( MIN ( OINV[DocDate]) )
VAR MaxDate = YEAR ( MAX ( OINV[DocDate]) )
RETURN
ADDCOLUMNS(
FILTER (
CALENDARAUTO(),
YEAR ([Date] ) >= MinDate &&
year ([Date] ) <= MaxDate
),
"Year", YEAR([Date]),
"Quarter", FORMAT ([Date], "\QQ" ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ([Date])
)
Any help appreciated, perhaps there is something I am missing in my understanding here! 🙂
Many thanks
