Divide and sum based on selected future dates

[jayped]

I would like to recreate an excel formula in dax as follows:

=IF(AND(Selected Value > Start Date , Selected Value < End Date)=TRUE,(Cost/Lifetime in Months),0)

I have a listing of assets which each have a start and end date of their useful life. I also have a calendar table containing future dates which I would like my slicer to be built on. When selecting a future month, quarter or fiscal year, I would like a measure that calculates the total amount for the selected period. So if an item's end date is June 30th 2023, I want to see the total incurred for fiscal year 2023 which would be the cost divided by lifetime in months * 6 and so forth for each item. 

Can someone help me with a solution? 

[danextian]

Hi @jayped ,

Can you please post a sample data and your expected result. It is hard to imagine what you are trying to achieve without seeing those information.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...

[jayped]

For simplicity, I'd have a table set up like below.

Item	Start Date	End Date	Lifetime in Months	Cost	Monthly (Cost/Lifetime)
Printer	1-Nov-19	31-Oct-22	36	6,000.00	166.67
Shredder	1-Jul-18	30-Jun-21	36	4,500.00	125.00
Television	1-Feb-22	31-Jan-25	36	4,000.00	111.11
Desk	1-Jun-21	31-May-26	60	3,500.00	58.33
Laptops	1-Aug-22	31-Jul-25	36	3,000.00	83.33

And I also have a calendar table containing dates up to 2027 and I'll create a couple slicers like below:

Upon selecting FY25 and Q3 (Jul-Sep 2025), how can I create a measure that calculates a total of 258.32 ((58.33 * 3) + 83.33) because for the desk, FY25 Q3 falls between the start and end date and for the laptops only one month can be accounted for because the end date is Jul 31.

Hope this helps.

[danextian · Accepted solution]

Hi @jayped ,

For this, Id use both DAX and Power Query.  Power Query to create a rows of start and end dates of the months included in the lifetime of the product and DAX to get the depreciation amount. Once the calculated rows fom PQ have been loaded, I can then use this measure to calculate for the depreciation.

Depreciation = 
VAR MinDate =
    MIN ( 'Calendar'[Start] )
VAR MaxDate =
    MAX ( 'Calendar'[End] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Monthly (Cost/Lifetime)] ),
        FILTER (
            'Table',
            'Table'[Start of Month] >= MinDate
                && 'Table'[End of Month] <= MaxDate
        )
    )

Here is a result screenshot of this approach.

Here's a sample pbix for your reference: https://drive.google.com/file/d/1YC_b0Ws0fi42p-VW0JUMl7jloMjd_Lg8/view?usp=sharing 

[jayped]

This is good. Thanks a mil!

[danextian]

Hi @jayped ,

Can you please post a sample data and your expected result. It is hard to imagine what you are trying to achieve without seeing those information.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message