Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Toussaint
New Member

Power BI

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

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

2 REPLIES 2
Toussaint
New Member

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

bhanu_gautam
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.