Forum Discussion

Toussaint's avatar
Toussaint
New Member
1 year ago
Solved

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...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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.