Lookup value to unrelated table, using min dates

[khatch]

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.

  

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

[Anonymous · Accepted solution]

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]
    )
)

[smpa01]

@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/2018

1/31/2018

[Anonymous]

Possible to upload some dummy data?

[khatch]

TranGLC Table:

Invoice Number	Invoice Line	GLAccount	Fiscal Year	TranDate
218422	1	42000|00|10	2018	8/13/2017
218423	1	42000|00|10	2018	7/30/2017
218424	1	42000|00|10	2018	11/10/2017
218425	1	42000|00|10	2018	10/3/2017
218426	1	42000|00|10	2018	9/21/2017
218427	1	42000|00|10	2018	7/9/2017
218427	2	42000|00|10	2018	10/10/2017
218428	1	42000|00|10	2018	3/26/2018
218429	1	42000|00|10	2018	11/26/2017
218430	1	42000|00|10	2018	9/11/2017
218431	1	42000|00|10	2018	4/14/2017
218432	1	42000|00|10	2018	1/4/2018
218433	1	42000|00|10	2018	11/14/2017
218434	1	42000|00|10	2019	4/21/2018
218435	1	42000|00|10	2019	4/24/2018
218436	1	42000|00|10	2019	7/6/2018
218437	1	42000|00|10	2019	10/31/2018
218437	2	42000|00|10	2019	4/23/2018
218437	3	42000|00|10	2019	4/1/2018
218438	1	42000|00|10	2019	5/11/2018
218438	2	42000|00|10	2019	5/11/2018
218439	1	42000|00|10	2019	5/25/2018
218440	1	42000|00|10	2019	7/13/2018
218441	1	42000|00|10	2019	7/1/2018
218442	1	42000|00|10	2019	7/20/2018
218442	2	42000|00|10	2019	5/15/2018
218442	3	42000|00|10	2019	8/16/2018
218442	4	42000|00|10	2019	5/25/2018
218442	5	42000|00|10	2019	9/14/2018

FiscalPer Table:

FiscalYear	FiscalPeriod	StartDate	EndDate
2018	1	4/2/2017	4/29/2017
2018	2	4/30/2017	5/27/2017
2018	3	5/28/2017	7/1/2017
2018	4	7/2/2017	7/29/2017
2018	5	7/30/2017	8/26/2017
2018	6	8/27/2017	9/30/2017
2018	7	10/1/2017	10/28/2017
2018	8	10/29/2017	11/25/2017
2018	9	11/26/2017	12/30/2017
2018	10	12/31/2017	1/27/2018
2018	11	1/28/2018	2/24/2018
2018	12	2/25/2018	3/31/2018
2019	1	4/1/2018	4/28/2018
2019	2	4/29/2018	5/26/2018
2019	3	5/27/2018	6/30/2018
2019	4	7/1/2018	7/28/2018
2019	5	7/29/2018	8/25/2018
2019	6	8/26/2018	9/30/2018
2019	7	10/1/2018	10/27/2018
2019	8	10/28/2018	11/24/2018
2019	9	11/25/2018	12/29/2018
2019	10	12/30/2018	1/26/2019
2019	11	1/27/2019	2/23/2019
2019	12	2/24/2019	3/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.

[v-piga-msft]

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