topic Re: Power BI in DAX Commands and Tips

Power BI

Toussaint — Thu, 03 Apr 2025 21:55:10 GMT

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

Re: Power BI

bhanu_gautam — Fri, 04 Apr 2025 06:14:20 GMT

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.

Re: Power BI

Toussaint — Mon, 07 Apr 2025 17:48:41 GMT

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