Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Members,
In the Matrix, I need to include a Period column, that is dynamic for each FY. This is achieved when I hard code using the FY however I want it automated.
Period1 - Apr01-May15 - 11/2months
Period2 - May16-Jun15 -1 month
Period3- Jun16-Jul15 -1 month
....
....
Period12 - Mar16-Mar31 -15 days
Please advice.
Thanks
Rightnow, this is used to get the PayPeriod
Pay Period =
if ((('Table'[Date]>=20210401) && ('Table'[Date] < 20210516)) || (('Table'[Date]>=20220401) && ('Table'[Date] < 20220516)) , "1-April",
if ((('Table'[Date]>=20210516) && ('Table'[Date] < 20210616))|| (('Table'[Date]>=20220516) && ('Table'[Date] < 20220616)),"2-May",
if ((('Table'[Date]>=20210616) && ('Table'[Date] < 20210716)) || (('Table'[Date]>=20220616) && ('Table'[Date] < 20220716)),"3-June",
if ((('Table'[Date]>=20210716) && ('Table'[Date] < 20210816))|| (('Table'[Date]>=20220716) && ('Table'[Date] < 20220816)),"4-July",
if ((('Table'[Date]>=20210816) && ('Table'[Date] < 20210916))|| (('Table'[Date]>=20220816) && ('Table'[Date] < 20220916)),"5-August",
if ((('Table'[Date]>=20210916) && ('Table'[Date] < 20211016))|| (('Table'[Date]>=20220916) && ('Table'[Date] < 20221016)),"6-September",
if ((('Table'[Date]>=20211016) && ('Table'[Date] < 20211116))|| (('Table'[Date]>=20221016) && ('Table'[Date] < 20221116)),"7-October",
if ((('Table'[Date]>=20211116) && ('Table'[Date] < 20211216))|| (('Table'[Date]>=20221116) && ('Table'[Date] < 20221216)),"8-November",
if ((('Table'[Date]>=20211216) && ('Table'[Date] < 20220116))|| (('Table'[Date]>=20221216) && ('Table'[Date] < 20230116)),"9-December",
if ((('Table'[Date]>=20220116) && ('Table'[Date] < 20220216))|| (('Table'[Date]>=20230116) && ('Table'[Date] < 20230216)),"10-Januar",
if ((('Table'[Date]>=20220216) && ('Table'[Date] < 20220316)) || (('Table'[Date]>=20230216) && ('Table'[Date] < 20230316)),"11-Febuary",
if ((('Table'[Date]>=20220316) && ('Table'[Date] <= 20220331))|| (('Table'[Date]>=20230316) && ('Table'[Date] < 20230331)),"12-March",
"0-Period"))))))))))))
Hi @SharonS
Is there regularity in your cycle, or can you provide some sample data, which is usually hard-coded
Best Regards!
Yolo Zhu
Thanks for reviewing my question.
The Periods vary only in the first and last period.
1st Period = April 1 - May 15
12th Period = Mar15 -Mar 31st
The rest of the periods are from 15th - 16th of next month which is constant.
May16-Jun15
Jun16-Jul15
Jul16-Aug15
Aug16-Sept15
Sept16-Oct15
Oct16-Nov15
Nov16-Dec15
Dec16-Jan15
Jan16-Feb15
Feb16-MAr16
Hope this helps.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |