Forum Discussion

khatch's avatar
khatch
Frequent Visitor
7 years ago
Solved

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.

  

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Possible to upload some dummy data?

    • khatch's avatar
      khatch
      Frequent Visitor
      7 years ago

      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.

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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