Forum Discussion
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 wo...
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] ) )
Possible to upload some dummy data?
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-msftResident Rockstar
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