Future expenses project

[jlarques]

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 

[jlarques]

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 · Accepted solution]

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.