DAX formula

[SuzieKidd]

Hello, I wonder if someone could help me. I have a table with people details and associated costs, these costs can span many years and I need to get the total cost in each fiancial year which is April to March, that the cost was open for. 

Cost Start	Cost End	Days Open 2022-2023	Days Open 2023-2024	Days Open 2024-2025
01/01/2022		365	365	365
01/04/2023	30/06/2024	0	366	91

I have created a formula that gets the total cost for each person based on the days that it was open, but when I add this into my visual it's only calculating those costs that opened in that year and does not calculate for each of the years that it was open.

Total Costs All =

VAR __maxDate = MAX(Calendar_Lookup[Date])

VAR __minDate = MIN(Calendar_Lookup[Date])

RETURN

CALCULATE(SUMX(CPLIFactor, CPLIFactor[Total Cost]), FILTER( CPLIFactor,CPLIFactor[Start Date] <= __maxDate && (__minDate < CPLIFactor[End Date] || ISBLANK(CPLIFactor[End Date]))))

Thank you in advance

Suzanne

[v-tangjie-msft]

Hi @SuzieKidd ,

Please try this:

Total Costs All =
VAR __maxDate = MAX(Calendar_Lookup[Date])
VAR __minDate = MIN(Calendar_Lookup[Date])

RETURN
CALCULATE(SUM(CPLIFactor[Total Cost]), FILTER( ALLSELECTED(CPLIFactor),CPLIFactor[Start Date] <= __maxDate && (__minDate < CPLIFactor[End Date] || ISBLANK(CPLIFactor[End Date]))))

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[rajendraongole1]

Hi @SuzieKidd - calculate the total cost for each financial year (April to March) that the cost was open,create a financial year column in your Calendar_Lookup table to differentiate between the financial years.

Financial Year =
IF(
MONTH(Calendar_Lookup[Date]) >= 4,
YEAR(Calendar_Lookup[Date]),
YEAR(Calendar_Lookup[Date]) - 1
)

create measure to calculate the total cost per financial year considering the days:

Days Open in FY =
VAR __fyStart = DATE(YEAR(Calendar_Lookup[Date]), 4, 1)
VAR __fyEnd = DATE(YEAR(Calendar_Lookup[Date]) + 1, 3, 31)
RETURN
CALCULATE(
SUMX(
CPLIFactor,
DATEDIFF(
MAX(__fyStart, CPLIFactor[Start Date]),
MIN(__fyEnd, COALESCE(CPLIFactor[End Date], __fyEnd)),
DAY
)
),
FILTER(
CPLIFactor,
CPLIFactor[Start Date] <= __fyEnd &&
(ISBLANK(CPLIFactor[End Date]) || CPLIFactor[End Date] >= __fyStart)
)
)

Create another measure for Proportional Cost:

Total Costs per FY =
VAR __fyStart = DATE(YEAR(Calendar_Lookup[Date]), 4, 1)
VAR __fyEnd = DATE(YEAR(Calendar_Lookup[Date]) + 1, 3, 31)
RETURN
CALCULATE(
SUMX(
CPLIFactor,
(CPLIFactor[Total Cost] / DATEDIFF(CPLIFactor[Start Date], COALESCE(CPLIFactor[End Date], TODAY()), DAY)) *
DATEDIFF(
MAX(__fyStart, CPLIFactor[Start Date]),
MIN(__fyEnd, COALESCE(CPLIFactor[End Date], __fyEnd)),
DAY
)
),
FILTER(
CPLIFactor,
CPLIFactor[Start Date] <= __fyEnd &&
(ISBLANK(CPLIFactor[End Date]) || CPLIFactor[End Date] >= __fyStart)
)
)

Try the above approach and let know , hope you already have a seperate date table.

By calculting the above measure  we will get the total cost per financial year based on the number of days the cost was open within that financial year

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[SuzieKidd]

Hi Rajendraongole, thank you so much for this, the financial year one worked really well, I already have a query to calculate the days as we have some weird ways of recording for example costs can open and close on the same day as a one off cost but is an actual weekly cost so I assign the days as 7.  The calculation query I'm getting the following when I tyr and create the measure.

"A single value for column 'Date' in table 'Calendar_Lookup' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."  Sorry if I'm being a bit dense, I'm still on a very steep learning curve.