Forum Discussion

BillyT_350's avatar
BillyT_350
Helper V
7 years ago

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

 

Please help. Thanks.

Need Help

6 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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

     

    • BillyT_350's avatar
      BillyT_350
      Helper V
      7 years ago

      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's avatar
        jdbuchanan71
        Super User
        7 years ago

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