DATESINPERIOD not counting the days in previous year

[Jayrx7]

Hello, Power BI Newbie here,

I am trying to create a measure that counts the rows of the rolling 365 days' record:

Below is my DAX:

24HR (Avg) =

 CALCULATE(COUNTROWS('CFS'), DATESINPERIOD('CFS'[DATES to USE], MAX('CFS'[DATES to USE]), -365, DAY))

However, it seems like the measure is only counting the rows from 1/1/2022 to MAX('CFS'[DATES to USE]), and not going all the way back to 9/15/2021. But when i do something like -7, or - whatever that doesn't exceed the number of days from 1/1/2022 to today it works fine. But anything exceeds that only gives me the year to date count.

My CFS table has data from 1/1/2021 to today.

Thank you in advance!

[TomMartens]

Hey @Jayrx7 ;

please try this statement:

CALCULATE(COUNTROWS('CFS'), DATESINPERIOD('CFS'[DATES to USE], CALCULATE(MAX('CFS'[DATES to USE])), -365, DAY))

Hopefully, this provides what you are looking for.

Regards,

Tom

[Jayrx7]

Thank you Tom.

I tried your solution, but it is giving me the same output as what I had. It doesn't go past 1/1/2022 into 2021's data somehow.

[TomMartens]

Hey @Jayrx7 ,

please create a pbix file that contains sample data but still reflects your data model (tables, calculated columns, relationships, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the Excel file as well.

Regards,

Tom

[Jayrx7 · Accepted solution]

Hi Tom,

I went back and check my data and realized I only pulled 2021's data from 1/1/2021 to 9/14/2021. I fixed my query and pulled the whole year's data and now the solution works.

Thank you so much!