Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
Hi there,
You can try this, but there are some assumptions here. Each Seq number has a unique starting and end dates, if it has two rows with the same starting date, it will take the lower number, same applies with the end date except it will take the max.
IF the case is that SalesDiff will always be a positiv number, i.e. Sales is always higher at the EndDate, you do not need to calculate the fd (first date) and ld. You can just compute startSales and endSales without the FILTER function.
This would be a calculated column:
SalesDiff = VAR seq = Table[Seq] VAR tbl = FILTER( Table, Table[Seq] = seq ) VAR fd = MINX( tbl, Table[BeginDate] ) VAR ld = MAXX( tbl, Table[EndDate] ) VAR startSales = MINX( FILTER( tbl, Table[BeginDate] = fd), Table[Sales] ) VAR endSales = MAXX( FILTER( tbl, Table[EndDate] = ld), Table[Sales] ) RETURN endSales - startSales
If this solves your problem please give a LIKE and mark as solved.
Regards,
Kristjan76
Hi there,
You can try this, but there are some assumptions here. Each Seq number has a unique starting and end dates, if it has two rows with the same starting date, it will take the lower number, same applies with the end date except it will take the max.
IF the case is that SalesDiff will always be a positiv number, i.e. Sales is always higher at the EndDate, you do not need to calculate the fd (first date) and ld. You can just compute startSales and endSales without the FILTER function.
This would be a calculated column:
SalesDiff = VAR seq = Table[Seq] VAR tbl = FILTER( Table, Table[Seq] = seq ) VAR fd = MINX( tbl, Table[BeginDate] ) VAR ld = MAXX( tbl, Table[EndDate] ) VAR startSales = MINX( FILTER( tbl, Table[BeginDate] = fd), Table[Sales] ) VAR endSales = MAXX( FILTER( tbl, Table[EndDate] = ld), Table[Sales] ) RETURN endSales - startSales
If this solves your problem please give a LIKE and mark as solved.
Regards,
Kristjan76
Hi Kris,
Your solution works exactly as i need. Great Thank you very much.
However when i applied this formula on my relational data the second MAX(value) is returning wrong, LD date is not matching because of which i am not getting correct sales value within the "Seq" group.
So i have created two columns
"Firstsales" : as column 1 based on your solution
------------------------------------------------------
Firstsales=
VAR seq = 'Table'[Seq]
VAR tbl =
FILTER(
'Table',
'Table'[Seq] = seq
)
VAR fd =
MINX(
tbl,
'Table'[BeginDate]
)
VAR startSales =
MINX(
FILTER( tbl, 'Table'[BeginDate] = fd),
'Table'[Sales]
)
RETURN
startSales
"Lastsale" as 2nd column formula as
-----------------------------------------
Lastsale=CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[Seq] =EARLIER('Table'[Seq])))
and Then created a 3rd SalesDiff column by ( Lastsale - Firstsale) to get the results as indicated by you. I know this sounds very strange but this is working.
Thanks again for your great help..!
I do not see any attached screen shot. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Unable to attach a screen shot intially, sorry for that. Please check the screen shot attached.
Thanks in advance.
Hi,
Will there always only be 2 rows per Seq?
Hi,
Thanks for the response, No seq varies based on the begin date and start date for that perticular fisical year/ calender year.
Thanks
Hi,
So the logic should be to subtract the value against the fartherst end date for a certain Seq and the value against the current row. Am i correct?
Yes, we can substract the value of the max date - min date within the sequence number group.
Hi,
Please take an example where you have more than two seq rows and show the expected result there.
Ahh, you are going to want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...