Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
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] ) )
@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 |
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |