Forecast projection over fiscal years/fiscal months.

[ThreeS]

FixedAmount is the average of the revenue from 3 past (full) months before previous month.

FixedAmount =

VAR StartDate = EOMONTH(TODAY(), -5)+1

VAR EndDate =  EOMONTH(TODAY(), -2)

VAR RevenueLast3Months =

    CALCULATE(

        SUM(Charge[AmountEUR]),

        Filter(

            DimDate,

            DimDate[Date] >= StartDate &&

            DimDate[Date] <= EndDate &&

            LASTDATE(Company[CUSContractEndDate]) >= TODAY()

            ),

        Charge[ChargeFromLevel]="XYZ"

    )

RETURN

--StartDate

--EndDate

divide(RevenueLast3Months,3)

Total amount forecasted for current fiscal year is calculated as:

ForecastCurrentYear =

VAR CurrentFiscalYearStart =

    IF(

        MONTH(TODAY()) >= 4,

        DATE(YEAR(TODAY()), 4, 1),

        DATE(YEAR(TODAY()) - 1, 4, 1)

    )

VAR CurrentFiscalYearEnd =

    IF(

        MONTH(TODAY()) >= 4,

        DATE(YEAR(TODAY()) + 1, 3, 31),

        DATE(YEAR(TODAY()), 3, 31)

    )

VAR AvgRevenueLast3Months = [FixedAmount]

VAR RemainingMonths =

    DATEDIFF(TODAY(), MAX(Company[CUSContractEndDate]), MONTH)

VAR RemainingMonthsFY =

    DATEDIFF(TODAY(), CurrentFiscalYearEnd, MONTH)

RETURN

IF(

    RemainingMonths > 0,

    If(

        RemainingMonths <= RemainingMonthsFY,

        AvgRevenueLast3Months * RemainingMonths,

        AvgRevenueLast3Months * RemainingMonthsFY

      ),

    BLANK()

  )

Measure ForecastCurrentYear create the correct amount, but doesn’t allow to slice by FiscalYears and FMonths. It sticks aalways  to the months used in FixedAmount. 
How to create a measure that allows visualization with customer names in rows, FY/FM months in columns, and fixed amount per customer (till contract end) as values for the current/next fiscal years.

[ThreeS · Accepted solution]

Hi,
***Thanks for your time***

The issue is solved in following steps::
1/To the Company table, a column is added which contains the amount which needs to be forecasted till end of contract.
The Company table contains CUSContractEndDate.

FixedAmountPerCustomer =

VAR StartDate = EOMONTH(TODAY(), -5)+1

VAR EndDate =  EOMONTH(TODAY(), -2)

VAR RevenueLast3Months =

    CALCULATE(

        SUM(Charge[AmountEUR]),

        FILTER(

            DimDate,

            DimDate[Date] >= StartDate &&

            DimDate[Date] <= EndDate &&

            LASTDATE(Company[CUSContractEndDate]) >= TODAY()

            ),

        Charge[ChargeFromLevel]="xyz"

    )

RETURN

DIVIDE(RevenueLast3Months, 3)

2/A forecast calendar table is created:

Forecast CAL =

VAR StartDate = date(YEAR(Today()),1,1)

VAR EndDate = date(YEAR(MAX(Company[CUSContractEndDate])),12,31)

RETURN

ADDCOLUMNS (

    CALENDAR (StartDate, EndDate),

    "Year", YEAR([Date]),

    "Month Number", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Quarter", QUARTER([Date]),

    "Day of Week", WEEKDAY([Date]),

    "Day Name", FORMAT([Date], "dddd"),

    "Day of Month", DAY([Date]),

    "Week Number", WEEKNUM([Date], 2), // ISO week number

    "FY", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1),

    "FM", IF(MONTH([Date]) >= 4, MONTH([Date]) - 3, MONTH([Date]) + 9)  

)

3/The filters in the crossjoin creates an entry in a table for each customer, for each month till end of contract.

Forecast TBL =

CROSSJOIN(

    Filter(

        Company,

        Company[CUSContractEndDate] > TODAY() &&

        Company[CUSContractEndDate] >= RELATED('Forecast CAL'[Date])

    ),

    FILTER(

        'Forecast CAL',

        'Forecast CAL'[Date] <= max(Company[CUSContractEndDate]) &&

        'Forecast CAL'[Day of Month] = 1

    )

)

4/ The measure below reduce FixedAmountPerCustomer only to the months till CUSContractEndDate+1

FixedAmount till ContractEnd = if('Forecast TBL'[Date]<= 'Forecast TBL'[CUSContractEndDate] +1 && 'Forecast TBL'[Date] > TODAY(), 'Forecast TBL'[FixedAmountPerCustomer])

5/The table “Forecast TBL” allows the required filtering with CustomerName in Rows, FY/FM in Columns and FixedAmountPerCustomer as Values in a matrix.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[v-yilong-msft]

Hi @ThreeS ,

It appears that you would like to create a measure in Power BI that allows visualization by customer name, fiscal year (FY), and fiscal month (FM) with a fixed amount per customer until the end of the contract as a value for the current and next fiscal year. Your current ForecastCurrentYear measure calculates the amounts correctly, but does not support the required slice and dice by FY/FM.

I think you first need to make sure that the DimDate table contains Fiscal Year (FY) and Fiscal Month (FM) columns. These columns should reflect the fiscal year starting in April. Use the matrix visualization in Power BI Desktop. Place the customer name in the row, the newly created FY and FM columns in the column, and the adjusted metric as the value. With this setup, you can slice and dice by FY and month as needed.

If that still doesn't work, you provide me with specific images or a .pbix file so I can dig deeper.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.