Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
SharonS
Frequent Visitor

Period Column for FY Apr- Mar with each period having different dates

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

3 REPLIES 3
SharonS
Frequent Visitor

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

v-xinruzhu-msft
Community Support
Community Support

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.