Power BI

[Toussaint]

my business process starts on [FiscalWeekCommencing] = 1st Monday of Month and End on following Sunday; my financial date table always start on 1st Apr of each year and contains [FiscalWeekCommencing] and [FiscalWeekEnding] columns. how to find the number of Fire tests of a facts table containing 'Fire Tests'[Date] column in a month on a table visual?

Mar-25 tests suppose to be 5 

06/03/2025.

12/03/2025

19/03/2025

26/03/2025

02/04/2025

[Toussaint]

Hi, Thanks for your solution, it helps a lot🙌

[bhanu_gautam · Accepted solution]

@Toussaint Create a calculated column for Fiscal Month:

dax
FiscalMonth =
VAR FiscalStart = DATE(YEAR([Date]), 4, 1)
RETURN
IF([Date] < FiscalStart,
MONTH([Date]) + 12 - MONTH(FiscalStart) + 1,
MONTH([Date]) - MONTH(FiscalStart) + 1)

Then create a measure to count the number of Fire tests in each fiscal month:

dax
FireTestsCount =
CALCULATE(
COUNT('Fire Tests'[Date]),
FILTER(
'Fire Tests',
'Fire Tests'[Date] >= MIN('Financial Date Table'[FiscalWeekCommencing]) &&
'Fire Tests'[Date] <= MAX('Financial Date Table'[FiscalWeekEnding])
)
)

Add the FiscalMonth column to your table visual.
Add the FireTestsCount measure to your table visual.