Decreasing value based on measure

[Ben1981]

Can't get this to work, any ideas?

I have a table with 2 columns of data: Month, Customer. The data runs from the beginging of the financial year and up to the previous month which will update monthly until the end of the financial year. I also have a measure in DAX that is an incremental value that I have forecasted for the full financial year. 

What I'm trying to do is create a table showing the full fin year and the customer values as a column but then using the incremental value, show the last customer count decrease to the end of the financial year based on the incremental value measure.

Example: 
Month - Cust - Increment
Sept 24 - 500 - 50 - Last actual
Oct 24 - 450 - 50 - future month
Nov 24 - 390 - 60 - furture month

[Kedar_Pande]

@Ben1981 

Create a new calculated table

FullFinancialYear = 
UNION(
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1, 12, 1), 
"Month", FORMAT(DATE(2024, [Value], 1), "mmm yy") 
),
"Customer", 0 
),
SELECTCOLUMNS(YourOriginalTable, "Month", YourOriginalTable[Month], "Customer", YourOriginalTable[Customer])
)

Create a new measure

Projected Customer Count =
VAR CurrentMonth = MAX(FullFinancialYear[Month])
VAR CurrentCustomerCount = CALCULATE(SUM(YourOriginalTable[Customer]), FILTER(YourOriginalTable, YourOriginalTable[Month] = CurrentMonth))
VAR IncrementValue = CALCULATE(SUM(IncrementalValue), FILTER(YourOriginalTable, YourOriginalTable[Month] = CurrentMonth))

RETURN
IF(
ISBLANK(CurrentCustomerCount),
BLANK(),
CurrentCustomerCount - 
SUMX(
FILTER(FullFinancialYear, FullFinancialYear[Month] > CurrentMonth),
[Incremental Value]
)
)

💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bhanu_gautam]

@Ben1981 , Try using below method

Create a Date Table:
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2024, 4, 1), DATE(2025, 3, 31)),
"MonthYear", FORMAT([Date], "MMM YY")
)

Then create a Forecast Table:
ForecastTable =
DATATABLE (
"MonthYear", STRING,
"Increment", INTEGER,
{
{"Oct 24", 50},
{"Nov 24", 60},
// Add more months as needed
}

Then Merge Tables:
MergedTable =
UNION (
SELECTCOLUMNS (
ExistingTable,
"MonthYear", ExistingTable[Month],
"Customer", ExistingTable[Customer],
"Increment", BLANK()
),

SELECTCOLUMNS (

           ForecastTable,

           "MonthYear", ForecastTable[MonthYear],

           "Customer", BLANK(),

           "Increment", ForecastTable[Increment]

       )

   )

Create a Calculated Column:
MergedTable =
MergedTable =
ADDCOLUMNS (
MergedTable,
"CustomerForecast",
VAR CurrentMonth = MergedTable[MonthYear]
VAR PreviousMonth =
CALCULATE (
MAX ( MergedTable[MonthYear] ),
MergedTable[MonthYear] < CurrentMonth
)
VAR PreviousCustomer =
CALCULATE (
MAX ( MergedTable[CustomerForecast] ),
MergedTable[MonthYear] = PreviousMonth
)
RETURN
IF (
ISBLANK ( MergedTable[Customer] ),
PreviousCustomer - MergedTable[Increment],
MergedTable[Customer]
)
)

This will create a table that shows the customer count decreasing based on the incremental value for each future month. You can then use this table in your Power BI report to visualize the forecasted customer count for the full financial year.