Forum Discussion

Ben1981's avatar
Ben1981
Helper III
1 year ago

Decreasing value based on measure

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

Need Help

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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.

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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