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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ThreeS
Frequent Visitor

Forecast projection over fiscal years/fiscal months.

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.

1 ACCEPTED SOLUTION
ThreeS
Frequent Visitor

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.

View solution in original post

3 REPLIES 3
ThreeS
Frequent Visitor

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yilong-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.