In power bi, I have created metrics to compare previous year by using CALCULATE([metric], DATEADD(Dates[Date], -1, YEAR)) function. Is it because of my Dates table? This only happens on the last day comparison when it's also the last day of data that I have.

Anyone else have this problem? And have you fixed it? Here is a screenshot: screenshot

My dates table is using the min and max dates from where I get the jobs from.

I have tried excluding today's date but it seems to happen when the last date that I have data for is equal to the last date of my dynamic date range.

Dates table:

Dates = 
VAR BaseTable = CALENDAR(MIN('Request Items'[Request Sign Off Date]),MAX('Request Items'[Request Sign Off Date]))
Return
    ADDCOLUMNS(
        BaseTable,
        "Year", YEAR([Date]),
        "Month", FORMAT([Date], "MMM"),
        "Month Number", MONTH([Date]),
        "Month Year", FORMAT([Date], "YYYY MM"),
        "Report Date", FORMAT([Date], "YYYY-MM-DD"),
        "Financial Year", IF(
                    MONTH([Date]) <= 9, 
                    CONVERT(YEAR([Date])-1, STRING) & "/" & CONVERT(YEAR([Date]), STRING),
                    CONVERT(YEAR([Date]), STRING) & "/" & CONVERT(YEAR([Date])+1, STRING)
                    ),
        "Month FY Order", IF(MONTH([Date]) <= 9, MONTH([Date])+3, MONTH([Date])-9),
        "Week Commencing", [Date] - WEEKDAY([Date], 3),
        "Weeks From This Week", DATEDIFF(TODAY() - WEEKDAY(TODAY(), 3), [Date] - WEEKDAY([Date], 3), WEEK),
        "Weekday Number", WEEKDAY([Date],2),
        "Weekday Name", FORMAT([Date], "dddd"),
        "Current FY", IF (IF(
                    MONTH([Date]) <= 9, 
                    CONVERT(YEAR([Date])-1, STRING) & "/" & CONVERT(YEAR([Date]), STRING),
                    CONVERT(YEAR([Date]), STRING) & "/" & CONVERT(YEAR([Date])+1, STRING)
                    ) = IF(
                    MONTH(TODAY()) <= 9, 
                    CONVERT(YEAR(TODAY())-1, STRING) & "/" & CONVERT(YEAR(TODAY()), STRING),
                    CONVERT(YEAR(TODAY()), STRING) & "/" & CONVERT(YEAR(TODAY())+1, STRING)
                    ), 1, 0),
        "Is Weekday", IF(WEEKDAY([Date]) = 1 || WEEKDAY([Date]) = 7, FALSE, TRUE),
        "Is Today", IF([Date]=TODAY(), 1, 0)
    )

Metrics

Accepted = distinctcount('Request Items'[Request ID])

Accepted LY = CALCULATE([Accepted], DATEADD(Dates[Date], -1, YEAR))

Accepted Cumulative = CALCULATE([Accepted], FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))

Accepted Cumulative LY = CALCULATE([Accepted LY], FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))

Accepted Rolling Average 7 Days = 
VAR DateFilter = 
    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -7, DAY)
VAR Result = 
    CALCULATE(AVERAGEX(VALUES(Dates[Date]), [Accepted]), DateFilter)
RETURN
    Result

Accepted Rolling Average 7 Days LY = CALCULATE([Accepted Rolling Average 7 Days], DATEADD(Dates[Date], -1, YEAR))

In power bi, I have created metrics to compare previous year by using CALCULATE([metric], DATEADD(Dates[Date], -1, YEAR)) function. Is it because of my Dates table? This only happens on the last day comparison when it's also the last day of data that I have.

Anyone else have this problem? And have you fixed it? Here is a screenshot: screenshot

My dates table is using the min and max dates from where I get the jobs from.

I have tried excluding today's date but it seems to happen when the last date that I have data for is equal to the last date of my dynamic date range.

Dates table:

Dates = 
VAR BaseTable = CALENDAR(MIN('Request Items'[Request Sign Off Date]),MAX('Request Items'[Request Sign Off Date]))
Return
    ADDCOLUMNS(
        BaseTable,
        "Year", YEAR([Date]),
        "Month", FORMAT([Date], "MMM"),
        "Month Number", MONTH([Date]),
        "Month Year", FORMAT([Date], "YYYY MM"),
        "Report Date", FORMAT([Date], "YYYY-MM-DD"),
        "Financial Year", IF(
                    MONTH([Date]) <= 9, 
                    CONVERT(YEAR([Date])-1, STRING) & "/" & CONVERT(YEAR([Date]), STRING),
                    CONVERT(YEAR([Date]), STRING) & "/" & CONVERT(YEAR([Date])+1, STRING)
                    ),
        "Month FY Order", IF(MONTH([Date]) <= 9, MONTH([Date])+3, MONTH([Date])-9),
        "Week Commencing", [Date] - WEEKDAY([Date], 3),
        "Weeks From This Week", DATEDIFF(TODAY() - WEEKDAY(TODAY(), 3), [Date] - WEEKDAY([Date], 3), WEEK),
        "Weekday Number", WEEKDAY([Date],2),
        "Weekday Name", FORMAT([Date], "dddd"),
        "Current FY", IF (IF(
                    MONTH([Date]) <= 9, 
                    CONVERT(YEAR([Date])-1, STRING) & "/" & CONVERT(YEAR([Date]), STRING),
                    CONVERT(YEAR([Date]), STRING) & "/" & CONVERT(YEAR([Date])+1, STRING)
                    ) = IF(
                    MONTH(TODAY()) <= 9, 
                    CONVERT(YEAR(TODAY())-1, STRING) & "/" & CONVERT(YEAR(TODAY()), STRING),
                    CONVERT(YEAR(TODAY()), STRING) & "/" & CONVERT(YEAR(TODAY())+1, STRING)
                    ), 1, 0),
        "Is Weekday", IF(WEEKDAY([Date]) = 1 || WEEKDAY([Date]) = 7, FALSE, TRUE),
        "Is Today", IF([Date]=TODAY(), 1, 0)
    )

Metrics

Accepted = distinctcount('Request Items'[Request ID])

Accepted LY = CALCULATE([Accepted], DATEADD(Dates[Date], -1, YEAR))

Accepted Cumulative = CALCULATE([Accepted], FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))

Accepted Cumulative LY = CALCULATE([Accepted LY], FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))

Accepted Rolling Average 7 Days = 
VAR DateFilter = 
    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -7, DAY)
VAR Result = 
    CALCULATE(AVERAGEX(VALUES(Dates[Date]), [Accepted]), DateFilter)
RETURN
    Result

Accepted Rolling Average 7 Days LY = CALCULATE([Accepted Rolling Average 7 Days], DATEADD(Dates[Date], -1, YEAR))

• powerbi
• dax

• How is the relationship defined between Dates and Request Items? How's is your matrix filtered to those three dates? Suggest focusing on why Accepted LY total is wrong in your matrix. Is your Dates table Marked as a Date table? – Sam Nseir Commented Jan 20 at 13:53
• I Marked as a Date table my Dates table and realised my relationship between Dates and Request Items wasn't correct and should have been on my Requests instead of Request Items (1-2-many relationship). Also made the adjustment in my Dates table to be dependent on Requests[Request Sign Off Date]. The total is still wrong; it still adding on a load of jobs accepted on the last day of last year in the graph and just in the total line in the matrix. – R Pillsbury Commented Jan 22 at 8:56
• Can you share a link to a PBIX (can be a minimal one with dummy data, just enough to demo the issue). – Sam Nseir Commented Jan 24 at 17:14
• limewire.com/d/… – R Pillsbury Commented Jan 27 at 12:41

1 Answer 1

Thanks for sharing a PBIX, it really helps. In these types of scenarios, it is good idea to create a few measures to help debug or understand what is going on. I created the following two measures:

_debugDates = 
  var minD = MIN(Dates[Date])
  var maxD = MAX(Dates[Date])
  return FORMAT(minD, "yyyy-MM-dd") & " ~ " & FORMAT(maxD, "yyyy-MM-dd")


_debugDates LY = CALCULATE([_debugDates], DATEADD(Dates[Date], -1, YEAR))

Then I dropped these in matrix...

And we can see why the total is wrong. The _debugDates LY range is including up to the end of the month. But why?

Your Dates table at the very least should include complete months, and ideally complete years. The DAX Time Intelligence functions expect this.

Update your Dates table with:

VAR BaseTable =
  CALENDAR(
    EOMONTH(MIN('Requests'[Request Sign Off Date]), -1) + 1,
    EOMONTH(MAX('Requests'[Request Sign Off Date]), 0))
  )

// or even better (recommended)
VAR BaseTable =
  CALENDAR(
    DATE( YEAR(MIN('Requests'[Request Sign Off Date])), 1, 1),
    DATE( YEAR(MAX('Requests'[Request Sign Off Date])), 12, 31)
  )

Either of the above will resolve the issue:

You probably want your Date slicer to show only valid dates, so create a new measure similar to:

# request row = COUNTROWS(Requests)

And add this measure as Filters on this visual on your Date slicer and set it to is not blank.