Calculate How Many Times a Text Value is displayed, within a Filter of another Column

[TimsLanding]

I've looked all over as I thought this would be an easy solution, but there are things I still cannot wrap my head around on what DAX can do, or can't do. 

I have a Spreadsheet that I'm designing, that will eventually calculate Possible Hours, within a Report Period. By Possible Hours, I mean, 24 hours a day, not work hours. To get to this point, I need to create a calculated column that displays the COUNT of how many Days are within the selected Report Period. The Report Period is by Month, but here's the catch, I had to create a Column that showed for example, "January" Report Period is Nov 16 - Dec 15. 

So here is what I have so far,

I'm trying to get a count of the Report Period Days, so if I could get a count of how many times "July" populates, which should be 31 (May 16 - June 15), it would achieve the desired result. I can then take that count of days and simply multiply it by 24 hours to get the Possible Hours within that Report Period. That's my next goal. 

What am I doing wrong so that I can count the amount of times July appears within that Year?

[v-xinruzhu-msft]

Hi @TimsLanding 

You can refer to the following example

Create the calculated column

DayInPeriod = var _filter=FILTER('Table',[Period]=EARLIER('Table'[Period])&&[Year]=EARLIER('Table'[Year]))
 var _mindate=MINX(_filter,[Date])
 var _maxdate=MAXX(_filter,[Date])
 return DATEDIFF(_mindate,_maxdate,DAY)+1

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TimsLanding]

Yolo Zhu, 

 This formula is very close, I only see the issue in the Report Period of February (Dec 16 - Jan 15), I'm guessing because of the YEAR change. It shows "365" Days DEC 16 - DEC 31, then "15" for Jan 1 - Jan 15, is there a protection we can add so that specific Period is calculated differently?

[v-xinruzhu-msft · Accepted solution]

Hi @TimsLanding 

Try the following calculated column

DayInPeriod = var _filter=FILTER('Table',[Period]=EARLIER('Table'[Period])&&OR(YEAR(EOMONTH([Date],1))=EARLIER('Table'[Year]),YEAR(EOMONTH([Date],1))=EARLIER('Table'[Year])+1))
return COUNTROWS(_filter)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TimsLanding]

You ROCK! It matches perfect with my Reference Column. I created a column IF, THEN, and IF "JULY" then 31, but I couldn't make 31 a value, it only took the text. So your method produces the value and I can use it for my measure. It's great, thanks again!

[TomMartens]

Hey @TimsLanding ,

create a pbix that contains sample data, upload the pbix to onedrive, google drive, or dropbox. Share the link. If you are using Excel to create the sample data, share the xlsx as well.

I assume your data will span multiple years, so what about January 2022, how can the days be detected before the get counted?

Regards,
Tom

[TimsLanding]

The Link to an Edited Dashboard with a replica DateTable

https://drive.google.com/file/d/1mOstGeXtON3Powq4iRwRmbKSXVOAIm_z/view?usp=sharing

I think it'll make more sense when you look at the Reporting Period. Our reporting period is for example JULY = MAY 16-JUN 15, AUG = JUN 16 - JUL 15...etc. So now I need to create a calculation either by measure or calculated column of how many days are in that reporting period, so I can use that day count and multiply it by 24 to get the possible hours of it. 

[TimsLanding]

The Link to an Edited Dashboard with a replica DateTable

https://drive.google.com/file/d/1mOstGeXtON3Powq4iRwRmbKSXVOAIm_z/view?usp=sharing

I think it'll make more sense when you look at the Reporting Period. Our reporting period is for example JULY = MAY 16-JUN 15, AUG = JUN 16 - JUL 15...etc. So now I need to create a calculation either by measure or calculated column of how many days are in that reporting period, so I can use that day count and multiply it by 24 to get the possible hours of it.