Custom Column Conundrum

[CorniTiger21]

I am trying to replicate this excel calculation in Power Bi to create a Period Offset column:- 

=[@Index]-XLOOKUP(TODAY(),[Date],[Index])

Can this be done as a Custom Column? 

Loving Power BI but very new to its calculations.

Regards

CorniTiger21

[v-jingzhang]

Hi @CorniTiger21 

Have you solved this problem? If yes, you can accept a helpful reply as solution to close this thread. Or post your own solution and accept it. 

If not, you can try the following solution with a calculated column. LOOKUPVALUE function (DAX)

PeriodOffset = 'Table (2)'[Period] - LOOKUPVALUE('Table (2)'[Period],'Table (2)'[Date],TODAY())

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[CorniTiger21]

Here is an extract of the table, a few columns removed for space. Happy for any solution, calculated column or custom column, etc. Sorry rookie error not supplying some data

Period Start Date	Period End Date	Index	Date	Period	Month	Year	CurYearOffset	MonthNum	PeriodOffset
01/04/2018	28/04/2018	1	01/04/2018	1	Apr	2018	-4	4	-60
01/04/2018	28/04/2018	1	02/04/2018	1	Apr	2018	-4	4	-60
01/04/2018	28/04/2018	1	03/04/2018	1	Apr	2018	-4	4	-60
01/04/2018	28/04/2018	1	04/04/2018	1	Apr	2018	-4	4	-60
01/04/2018	28/04/2018	1	05/04/2018	1	Apr	2018	-4	4	-60

[KNP]

What are your periods? Are you working with 4-5-4 or 13 week or similar?

I assume on the 29/04/2018 the Index changes to 2 and keeps incrementing?

What do you use the PeriodOffset for?

[CorniTiger21]

Hi KNP,

We work on 13 periods in a year, the Index column increase incrementatly, then i have a "mod" column to repeat the 13 periods. Period Offset will allow me to future forecast, ie work in the next 6 periods, and to enable calculations to be made on finance period on period variance.

[KNP]

Ok, makes sense.

I think the code I posted above might do the trick.

Did you have a chance to try it?

[KNP · Accepted solution]

Something like this might work.

Go to Transform Data and paste this into the advanced editor of a blank query...

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjDUNzDRNzIwtFDSUTKyQOIYAjGKLEjAsaAIpA4ioGsCJEBY18xAKVaHoGFG1DTMmJqGmVDTMFPiDYsFAA==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [
                #"Period Start Date" = _t,
                #"Period End Date" = _t,
                Index = _t,
                Date = _t,
                Period = _t,
                Month = _t,
                Year = _t,
                CurYearOffset = _t,
                MonthNum = _t,
                PeriodOffset = _t
            ]
    ),
    ChangeType = Table.TransformColumnTypes(
        Source,
        {
            {"Period Start Date", type date},
            {"Period End Date", type date},
            {"Date", type date},
            {"Index", Int64.Type},
            {"Period", Int64.Type},
            {"Year", Int64.Type},
            {"CurYearOffset", Int64.Type},
            {"MonthNum", Int64.Type},
            {"PeriodOffset", Int64.Type}
        }
    ),
    AddDaysInPeriod = Table.AddColumn(
        ChangeType,
        "DaysInPeriod",
        each Number.RoundTowardZero(Duration.TotalDays([Period End Date] - [Period Start Date])) + 1,
        Int64.Type
    ),
    AddNewPeriodOffset = Table.AddColumn(
        AddDaysInPeriod,
        "NewPeriodOffset",
        each
            Number.RoundTowardZero(
                Duration.TotalDays([Period Start Date] - DateTime.Date(DateTime.FixedLocalNow())) / [DaysInPeriod]
            ),
        Int64.Type
    )
in
    AddNewPeriodOffset

[CorniTiger21]

Sorry for the late responce i only got chance to try this last week, thank you the calculation worked very well.

Thank you

[KNP]

Would be good to provide some sample data/example output so we don't have to create it manually.

Typically, I'd recommend doing this in Power Query but if you want to create a calculated column, you could look into the OFFSET function. 

Intellisense doesn't work for it yet as it is relatively new but there's a good article here...

https://data-marc.com/2022/09/21/how-offset-in-dax-will-make-your-life-easier/

Alternately, if you provide some sample data and expected output, I'll see if I can offer a PQ solution.