Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Community,
Greetings!
I am running into some serious issue, I have a data set which has columns shipstart, shipend and profit amount,
1) Based on this data set I wnat to calculate if date is in between 1/8/2022 and 31/7/2023 I want sum of Profit amount this is easy part.
2) Tricky part is, if my shipend date is after 31/7/2023, First I have to calculate number of months between shipstart and shipend date than divide profit amount by total month .
3) Calculate total months between shipstart date and 31/7/2023 than multiply it with profit amount which I got from point 2.
4) The total should show combination of all 3 points.
Ship start | Ship end | Approved offers $ | Total months from shipstart to 7/31/2023 | Total number of months | Profit amount/Total number of month | H8*F8 | ||
8/1/2022 0:00 | 8/31/2022 0:00 | $22,476 | $22,476 | |||||
8/1/2022 0:00 | 9/1/2022 0:00 | $3,892 | $3,892 | |||||
8/1/2022 0:00 | 9/29/2022 0:00 | $2,788 | $2,788 | |||||
8/1/2022 0:00 | 12/31/2022 0:00 | $22,653 | $22,653 | |||||
8/1/2022 0:00 | 3/1/2023 0:00 | $18,488 | $18,488 | |||||
8/1/2022 0:00 | 7/31/2023 0:00 | $205,340 | $205,340 | |||||
8/1/2022 0:00 | 9/1/2023 0:00 | $3,644 | $3,123.43 | 12 | 14 | $260.29 | $3,123.43 | |
8/2/2022 0:00 | 3/31/2022 0:00 | $22,668 | $22,668 | |||||
8/2/2022 0:00 | 12/30/2022 0:00 | $31,805 | $31,805 | |||||
8/2/2022 0:00 | 12/31/2022 0:00 | $16,899 | $16,899 |
I hope you got my question and help me to resolve this issue.
Thanks in advance for your help and time!
Solved! Go to Solution.
@Anonymous Maybe:
Column =
VAR __ShipStart = [Ship start]
VAR __ShipEndDate = [Ship end]
VAR __Offer = [Approved offers $]
VAR __TotalMonths = DATEDIFF( __ShipStart, __ShipEnd, MONTH ) + 1
VAR __TotsMonths = DATEDIFF( __ShipStart, DATE( 2023, 7, 31 ) + 1
VAR __Result = DIVIDE( __Offer, __TotalMonths ) * __TotsMonths
RETURN
__Result
@Anonymous Maybe:
Column =
VAR __ShipStart = [Ship start]
VAR __ShipEndDate = [Ship end]
VAR __Offer = [Approved offers $]
VAR __TotalMonths = DATEDIFF( __ShipStart, __ShipEnd, MONTH ) + 1
VAR __TotsMonths = DATEDIFF( __ShipStart, DATE( 2023, 7, 31 ) + 1
VAR __Result = DIVIDE( __Offer, __TotalMonths ) * __TotsMonths
RETURN
__Result
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |