cancel
Showing results for
Did you mean:
Member

## DatesInPeriod() Broken?

I'm trying to do a calculation using dates in period.

`DATESINPERIOD('Cal'[Date], TODAY(), -1, YEAR)`

Seems like it should return a column of dates between/including the applicable dates. Instead, I get (BLANK).

Also, how can I calculate how many days are in that time oeriod? Yes, hypothetically it should be 365 except during leap years, but I want it to be dynamic.

DATEADD() and DATESBETWEEN() don't seem to want to cooperate. You can use neither:

`DATEDIFF(TODAY(), TODAY() - 1 YEAR, DAY)`

nor

`DATESBETWEEN('Cal'[Date], DATEADD(TODAY(), - 1, YEAR), TODAY())`

6 REPLIES 6
New Contributor

## Re: DatesInPeriod() Broken?

First question, does your 'Cal' table include the dates from 2/1/2018 through 1/30/2019?  If not it will not be able to calc DATESINPERIOD, even if it is only missing 1/30/2019 it will still fail.

To test, you should be able to select Modeling > New Table and define your table with your measure:

If you don't see something like the above image and instead you see something like below, you are missing dates in your 'Cal'[Date] column

As far as counting the days you can use

`Days = COUNTROWS( DATESINPERIOD(Dates[Date], TODAY() , -1 ,YEAR ) )`

since DATESINPERIOD returns a table

Member

## Re: DatesInPeriod() Broken?

@jdbuchanan71 Thank you for your suggetsion. However, I tried that and somehow got 338, instead of 365.
My date table looks fine; it is set up to include all dates from the last two calendar years. Any idea what might be going on?

Get this: it's been 338 days since February 28th. Somehow it's not getting that you can go from February 28th to March 1st. I can see that my calendar table is fine, but when I create a new table as you suggest, it only starts in March 1st. That seems like a bug.

So I suppose you did supply me with the solution, PBI is just too dumb to implement it?

Highlighted
New Contributor

## Re: DatesInPeriod() Broken?

@jengwtIs it possible there is some other filter that is restricting the number of dates getting fed into the measure?

try creating a new table using your measure (the one that gave you 338) and just take the countrows off

`Days = COUNTROWS( DATESINPERIOD(Dates[Date], TODAY() , -1 ,YEAR ) )`

Select modeling > New Talbe and put in

`test_table = DATESINPERIOD(Dates[Date], TODAY() , -1 ,YEAR )`

But put in the DATESINPERIOD( ) portion of your measure.  That will give you a table of the exact dates your measure is counting and should give you an idea of where your problem is coming from.

You can the create a new tab in your report, drop in a matrix and pull that table in to see if maybe there is a report level filter limiting your dates.

Member

## Re: DatesInPeriod() Broken?

@jdbuchanan71 I do have date filters in my report, but none of them are active, and I wouldn't think they would affect table creation.

New Contributor

## Re: DatesInPeriod() Broken?

@jengwt I had missed your note on the Feb - Mar but I was able to replicate the error you are seing if I have the last date on my date table is today, 2/1/2019.  If I extend my date table out to 12/31/2019 it calculates correctly:

With today as the last day:

With 12/31/2019 as the last day

Member

## Re: DatesInPeriod() Broken?

Yah, what you did in your first example should have worked, but isn't.

This still seems buggy to me. I opened an issue thread about it.