Forum Discussion
Referencing Signal Row Against Entire Column
Hi some_bih,
I can do this in Excel but not sure how to transfer it to BI. To clarfiy what I mean I have included a table below.
To determine Days +/-, I have subtracting the Actual date (Column F) from the Baseline Date (Column D). This will give me how many days I am passed our contracted date.
To determine how many weeks is passed I am subtracting all of Column F from D1, which is highlight in yellow (11/7/22). So I would need to do the same formula but instead of referencing all of Column D, I just want to reference D1, against all of Column F.
Hopefully this explains my issue a little more.
- some_bih
Community Champion
Hi Anonymous in Power BI there is something called calculated columns, similar to normal column in Excel so as I underdstan you can:
a) create calculated column for difference (name as you wish) and use formula
INT (Actual date (Column F) - (minus) Baseline Date (Column D)), this will be calculate row by row
b) you wrote "I just want to reference D1, against all of Column F" similar manner as you want single "cell" D1 to deduct by Column F entire, again you need calculated column (name as you wish) with formula which should use some aggregation, like SUM, MIN, MAX... Now I see your D and F column is date, this add complexity as "you can not sum dates" 😞 so another solution should be found.
In general, in Power BI, when you work with dates / weekday, months... usually there is Calendar / Date table so you connect it with Data table (amounts etc.) and do calculation. Using slicer / filter for Dates level you wish results are automatically recalculated.
So you need to go to your model / data and think what you need to calculate, keeping in mind that dates featues Power BI will handle.
Hope this help / kudos appreciated.
