Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Proud to be a Super User!
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.
Proud to be a Super User!
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.
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
Proud to be a Super User!
This is good. Thanks a mil!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |