The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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.
Proud to be a Super User! |
|
Hi, Thanks for your solution, it helps a lot🙌
@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.
Proud to be a Super User! |
|