Forum Discussion

jlarques's avatar
jlarques
Helper V
1 year ago
Solved

Future expenses project

Dear community,

I have many rows with start and finish dates. I want to project expenses(cost+salary) for future months. For instance, we are in november and I want to project expenses for december. The question is that I don't want to include any employee that its end date is less than 31/12/2024, becuse this date means that the employee not finish its relationship with the company.

I tried to create this measure, but the result is blank.

How can I do this? 

Thanks to everybody.

 

José Luis 

 

 

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Hi jlarques ,

    To project expenses for a future month (e.g., December 2024) while excluding employees whose END DATE is earlier than 31/12/2024, you can modify your DAX measure as follows:

    Corrected DAX Measure

    ProjectedExpenses =
VAR _FechaFin = DATEVALUE("31/12/" & YEAR(TODAY()))
VAR Result =
    CALCULATE(
        SUM(factPuestos[Cost]) + SUM(factPuestos[Salary]),
        FILTER(
            factPuestos,
            factPuestos[END DATE] >= _FechaFin
        )
    )
RETURN
    Result

    Explanation:

    1. _FechaFin Calculation:

      • Defines 31/12/2024 dynamically using the current year. Replace YEAR(TODAY()) with a specific year if necessary.

    2. FILTER Logic:

      • Ensures rows are included only if the END DATE is greater than or equal to 31/12/2024.

    3. Summation:

      • Adds the Cost and Salary columns for the filtered rows.

    4. SUM vs. VALUES:

      • Use SUM instead of VALUES to calculate total expenses, as VALUES will return a single value or an error for multiple rows.

    Troubleshooting:

    • Ensure factPuestos[END DATE] is in a proper date format. If it is stored as text, convert it using DATEVALUE.
    • If the measure still returns blank, verify that:
      • There are rows where END DATE >= 31/12/2024.
      • Cost and Salary columns contain numeric values.

    Let me know if you face further issues!

     

    Please accept this as solution if it helps. Appreciate Kudos.

2 Replies

  • jlarques's avatar
    jlarques
    Helper V
    1 year ago

    Thank you so much FarhanJeelani !

    your explanation contains important details to understand what was the problem. Now it works and I have my specific calculation for each employee.

     

    Thanks again!

     

     

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    Hi jlarques ,

    To project expenses for a future month (e.g., December 2024) while excluding employees whose END DATE is earlier than 31/12/2024, you can modify your DAX measure as follows:

    Corrected DAX Measure

    ProjectedExpenses =
VAR _FechaFin = DATEVALUE("31/12/" & YEAR(TODAY()))
VAR Result =
    CALCULATE(
        SUM(factPuestos[Cost]) + SUM(factPuestos[Salary]),
        FILTER(
            factPuestos,
            factPuestos[END DATE] >= _FechaFin
        )
    )
RETURN
    Result

    Explanation:

    1. _FechaFin Calculation:

      • Defines 31/12/2024 dynamically using the current year. Replace YEAR(TODAY()) with a specific year if necessary.

    2. FILTER Logic:

      • Ensures rows are included only if the END DATE is greater than or equal to 31/12/2024.

    3. Summation:

      • Adds the Cost and Salary columns for the filtered rows.

    4. SUM vs. VALUES:

      • Use SUM instead of VALUES to calculate total expenses, as VALUES will return a single value or an error for multiple rows.

    Troubleshooting:

    • Ensure factPuestos[END DATE] is in a proper date format. If it is stored as text, convert it using DATEVALUE.
    • If the measure still returns blank, verify that:
      • There are rows where END DATE >= 31/12/2024.
      • Cost and Salary columns contain numeric values.

    Let me know if you face further issues!

     

    Please accept this as solution if it helps. Appreciate Kudos.