Hi, I will try to sketch my approach, hope this helps:
a) I assume that you want to create a base table in Power BI and then just apply the visual and this should give the right amounts per year in your bar chart.
b) the strategy will be to do create an additional table to join them together (call it "carry over table") and then do result = UNION(Table A, "carry over table") and apply the visual
- create a work table temp = TableA
- esablish a key relationship between temp and TableB via reference
- in temp create a new column purchaseRef = RELATED (TableB['Purchase']) this gives the information of TableB if to carry over or not.
- in temp create a new column:
newDate =
var transactionYear = year(temp['Transaction Date'])
var thisYear = year(today())
var thisRef = temp['purchaseRef']
return
if(thisRef = "Purchase" && transactionYear < thisYear, Date(transactionYear + 1, 1, 1)
So, in the helper table the amount is carried over to the first of the next year if the criteria are met.
Final helper table:
carry over table = selectcolumns(filter(temp, isblank([newDate] = false)),
"Reference", temp['Reference'],
"Value", temp['Value'],
"Date", temp['newDate'])
result = UNION(Table A, "carry over table"). You name the result after your visual with "VIEW" or so, to make clear that this is solely attached to the visual.
So, basically artifical records for the visual are created that carry the first of the next year and the amount to carry over. Or your logic goes in the newDate table column of the helper table.
The alternative is to create a table with the distinct years you have and do a more complicated calculated column to get the right amount per year. Matter of taste.
Best regards
Christian
