Forum Discussion
split value equally into months and cumulative
Sorry I think I posted this earlier but I cannot find it anywhere.
So I repost again
Need help with my measure here:
AVG Contract Hours =
VAR _MONTHSTART =
MIN ( 'Date'[Date] )
VAR _datediff =
VAR startDate =
CALCULATE( MIN( 'BOH Hours'[Start] ),
ALLEXCEPT( 'BOH Hours', 'BOH Hours'[PO] )
)
VAR endDate =
CALCULATE( MAX( 'BOH Hours'[End] ),
ALLEXCEPT( 'BOH Hours', 'BOH Hours'[PO] )
)
RETURN
DATEDIFF(startDate, endDate, MONTH)+1
VAR _ActualConQTY =
MINX(
FILTER(
SELECTCOLUMNS(
ALLSELECTED('BOH Hours'),"index",'BOH Hours'[Index],"Min",MINX(
FILTER(
'BOH Hours','BOH Hours'[PO]=EARLIER('BOH Hours'[PO])),
'BOH Hours'[Contract Qty])),[index]=MAX('BOH Hours'[Index])),[Min])
VAR _AVG =
DIVIDE (_ActualConQTY, _datediff )
RETURN
IF (
_MONTHSTART >= MAX ( 'BOH Hours'[Start] )
&& _MONTHSTART < MAX ( 'BOH Hours'[End] ),
_AVG
)
First I need to find the number of months between the start and end date using DATEDIFF.
Next I need to find the actual Contract Qty (Its not the SUM of all the same PO number)
Then I divide the Contract Qty and DATEDIFF to get the avarage per month.
Result is below but I need it to be cumulative. What should I do to make it cumulative?
I cannot upload Pbix so I am not able to show sample but if needed here is a part of my table here.
1 Reply
- AnonymousNot applicable
Hi Yuiitsu ,
Regarding sharing the file, you can upload it to the cloud and then share the link to it, taking care to make the link permissions public and that the file does not contain private information.
How to provide sample data in the Power BI ForumBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum