Reply
Frequent Visitor
Posts: 2
Registered: ‎05-18-2017
Accepted Solution

Lookup value to unrelated table, using min dates

[ Edited ]

This is a seemingly very simple question, but I cannot seem to find an answer.  I have two tables, one with transaction dates and the other with a calendar of fiscal period start and end dates.  I would like to add a calculated column (so it can be used as a slicer) to the transactions table showing the fiscal period.

  TranGLC.JPGFiscal Period.JPG

 

I'm thinking min might be the right function, but I could be way off.


Accepted Solutions
Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Lookup value to unrelated table, using min dates

You can use the following DAX to get the fiscal period.

 

FiscalPeriod =
CALCULATE (
    VALUES ( FYear[FiscalPeriod] ),
    FILTER (
        FYear,
        'Tran'[TranDate] >= FYear[StartDate]
            && 'Tran'[TranDate] <= FYear[EndDate]
    )
)

2018-11-07_11-42-21.png

 

View solution in original post


All Replies
Established Member
Posts: 135
Registered: ‎10-26-2018

Re: Lookup value to unrelated table, using min dates

Possible to upload some dummy data?

Frequent Visitor
Posts: 2
Registered: ‎05-18-2017

Re: Lookup value to unrelated table, using min dates

[ Edited ]

TranGLC Table:

Invoice NumberInvoice LineGLAccountFiscal YearTranDate
218422142000|00|1020188/13/2017
218423142000|00|1020187/30/2017
218424142000|00|10201811/10/2017
218425142000|00|10201810/3/2017
218426142000|00|1020189/21/2017
218427142000|00|1020187/9/2017
218427242000|00|10201810/10/2017
218428142000|00|1020183/26/2018
218429142000|00|10201811/26/2017
218430142000|00|1020189/11/2017
218431142000|00|1020184/14/2017
218432142000|00|1020181/4/2018
218433142000|00|10201811/14/2017
218434142000|00|1020194/21/2018
218435142000|00|1020194/24/2018
218436142000|00|1020197/6/2018
218437142000|00|10201910/31/2018
218437242000|00|1020194/23/2018
218437342000|00|1020194/1/2018
218438142000|00|1020195/11/2018
218438242000|00|1020195/11/2018
218439142000|00|1020195/25/2018
218440142000|00|1020197/13/2018
218441142000|00|1020197/1/2018
218442142000|00|1020197/20/2018
218442242000|00|1020195/15/2018
218442342000|00|1020198/16/2018
218442442000|00|1020195/25/2018
218442542000|00|1020199/14/2018

 

FiscalPer Table:

FiscalYearFiscalPeriodStartDateEndDate
201814/2/20174/29/2017
201824/30/20175/27/2017
201835/28/20177/1/2017
201847/2/20177/29/2017
201857/30/20178/26/2017
201868/27/20179/30/2017
2018710/1/201710/28/2017
2018810/29/201711/25/2017
2018911/26/201712/30/2017
20181012/31/20171/27/2018
2018111/28/20182/24/2018
2018122/25/20183/31/2018
201914/1/20184/28/2018
201924/29/20185/26/2018
201935/27/20186/30/2018
201947/1/20187/28/2018
201957/29/20188/25/2018
201968/26/20189/30/2018
2019710/1/201810/27/2018
2019810/28/201811/24/2018
2019911/25/201812/29/2018
20191012/30/20181/26/2019
2019111/27/20192/23/2019
2019122/24/20193/31/2019

 

I'm looking to add the FiscalPer to the TranGLC table based on TranDate.  I'm like to use a calculated column so I can use it as a slicer.

Member
Posts: 131
Registered: ‎03-27-2017

Re: Lookup value to unrelated table, using min dates

[ Edited ]

@khatchThe 2nd Table has Start and End Date by Fiscal Year and Period.

 

Now we need to add a custom column as following. It will create a column which will capture all the dates by Fiscal Year_Period interval.

 

Then it becomes a piece of cake. You can Join Table 1 and Table 2 respectively by TranDate and Custom Column to get the Fiscal Year, Fiscal Period.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start])..Number.From([End])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

I created my code based on the following data

 

StartEnd

1/1/20181/31/2018

 

 

Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Lookup value to unrelated table, using min dates

You can use the following DAX to get the fiscal period.

 

FiscalPeriod =
CALCULATE (
    VALUES ( FYear[FiscalPeriod] ),
    FILTER (
        FYear,
        'Tran'[TranDate] >= FYear[StartDate]
            && 'Tran'[TranDate] <= FYear[EndDate]
    )
)

2018-11-07_11-42-21.png

 

Community Support Team
Posts: 2,674
Registered: ‎02-06-2018

Re: Lookup value to unrelated table, using min dates

Hi @khatch,

 

Have you solved your problem with the formula from Jessica_Seiya?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please share your desired output so that we could help further on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.