cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khatch Frequent Visitor
Frequent Visitor

Lookup value to unrelated table, using min dates

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jessica_Seiya Established Member
Established Member

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

 

5 REPLIES 5
Nick_M Senior Member
Senior Member

Re: Lookup value to unrelated table, using min dates

Possible to upload some dummy data?

khatch Frequent Visitor
Frequent Visitor

Re: Lookup value to unrelated table, using min dates

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.

smpa01 Established Member
Established Member

Re: Lookup value to unrelated table, using min dates

@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

 

 

Jessica_Seiya Established Member
Established Member

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
Community Support Team

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.