Monthly breakdown of numbers

Question

I'm extracting data from the database where all the dates fields exit based on the Fiscal Year (starting from October) and Fiscal Month. I have 2 date fields based on the received date and closed date and extracting separate received date FY and closed date fiscal year. I have calculated years numbers based on the condition which works fine. Now, I want to break it down by the month where I'm experiencing wired issue such as its missing current year one month (October) data for few records else it works fine. I checked everything in terms of data wise, everything looks good. I want to know if I'm using the right formula for this? Attaching a data model for your reference.

this is the formula I'm implementing.

CALCULATE(COUNT(table[id]), 
                          FILTER(ALLEXCEPT(table,  table[office], table[Fiscal_month_Closed]), 
                         (table[Fiscal_Year_Closed] = SELECTEDVALUE(table[Fiscal_Year])))

Above formula works fine for the year selection. It doesn't provide correct values while adding closed month (table[Fiscal_month_Closed]) in here.

I want to counts ids by office where closed year is equal to selected value of the fiscal year and break it down by month_closed.

Thanks in advance.

Answer 1

Try the below:

CALCULATE(
  COUNT(table[id]),
  ALLEXCEPT(table, table[office], table[Fiscal_month_Closed]), 
  table[Fiscal_Year_Closed] = table[Fiscal_Year]
)