CODEWITHSUNDEEP
powerbidax
haziq jaafar
haziq jaafar

Reputation: 23

Last 30 days Data Not Able to Include Previous Year Date

while working on my weekly report, I've encountered an issue for the first time. I suspect that the function used to measure 'last 30 days' is unable to include data from the previous year as we enter the new year. I observed this when changing the 'Data As of' date to a more later date and seeing that it only includes 'Last 7 days' of the current year. I would like the function to include data from the previous year as of January, and I believe this issue will no longer be present in February.

enter image description here

this is the measure created for number of open cases in the last 30 days, if it helps

enter image description here

I tried using the "DATESINPERIOD" function but still obtain the same error.

Upvotes: 0

Views: 140

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

DATEADD only returns dates that are in the range of the given column. Since you are using
.[Date] you are/referencing the hidden date table that is created when using Auto date/time under the Time intelligence setting under Options, and this hidden table is always the full year (1 Jan to 31 Dec).

If your Data as of table has dates from last year, then you should drop .[Date].

Last 30 days = DATEADD('Data as of'[Data as of], -29, DAY)

Or simply just have:

Last 30 days = [Data as of] - 29

Or even drop the Calculated Columns altogether, and in your Measure just have:

Open Cases (in the last 30 days) = 
  var endDate = MIN('Data as of'[Data as of])
  var startDate = endDate - 29
  var casesCount =
    CALCULATE(
      [TOTAL Cases],
      startDate <= 'Cases'[Created date] && 'Cases'[Created date] <= endDate 
    )
  RETURN COALESCE( casesCount , 0 )

Upvotes: 0

Related Questions