Get total num of days in the Quarter

[Anonymous]

Hi, I have a table that looks like this and I want to create a measure to get the total number of days in the quarter (i.e. FY21-Q4) which is 30+31+30 = 91. Does anyone know how I can get 91?

FYI, Fiscal Quarter is a slicer in my dashboard page

Regards,
BK

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous ,

You could create a calendar, then extract his FY-QUARTER column, and finally COUNT the rows of quarter, create measures by the following formula：
First create table:

Newtable = CALENDARAUTO(6)

Then create column:

FY-Qua = "FY"&RIGHT(IF(MONTH([Date])>=7,YEAR([Date])+1,YEAR([Date])),2)&"-Q"&SWITCH(QUARTER([Date]),3,1,4,2,1,3,2,4)

Last create measure:

Quartertotal = CALCULATE(COUNTROWS('Newtable'),FILTER('Newtable',[FY-Qua] in VALUES('Table'[Fiscal Quarter])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Get a distinct list of "week"  values (which actually seem to be months), then get the average of days for each of them, and finally add them up.

Or, use a proper calendar table based on days, not on weeks.