DatesInPeriod() Broken?

[jengwt]

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())

Please help. Thanks.

[jdbuchanan71]

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

[jengwt]

@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?

[jdbuchanan71]

@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

So instead of 

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.

[jengwt]

@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.

Did you see my new comments about Feb - Mar? I had tried what you suggested.

[jdbuchanan71]

@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

[jengwt]

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.