cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bice_cold Regular Visitor
Regular Visitor

Payroll Number Semi-Monthly

I would like to create a column using dax that gives me a count of payroll, starting over each year and increasing semi-monthly on the 1st and 16th of each month. For example, the date table will show:

 

 

1/11/20191
1/12/20191
1/13/20191
1/14/20191
1/15/20192
1/16/20192
1/17/20192
1/18/20192
1/19/20192
1/20/20192
1/21/20192
1/22/20192
1/23/20192
1/24/20192
1/25/20192
1/26/20192
1/27/20192
1/28/20192
1/29/20192
1/30/20192
1/31/20192
2/1/20193
2/2/20193
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Payroll Number Semi-Monthly

Try this

NewCol =
VAR Base_ = (2 * ( MONTH ( CalendarTable[Date] ) - 1 )) + 1
RETURN
SWITCH (TRUE ();
DAY ( CalendarTable[Date] ) < 16; Base_;
Base_ + 1
)

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Payroll Number Semi-Monthly

Hi @bice_cold 

Try this for a calculated column:

 

NewCol =
SWITCH (
    TRUE (),
    VAR Base_ = (2 * ( MONTH ( Table1[Date] ) - 1 )) + 1
    RETURN
        DAY ( Table1[Date] ) < 16, Base_,
    Base_ + 1 
)

 

bice_cold Regular Visitor
Regular Visitor

Re: Payroll Number Semi-Monthly

Thanks for the response @AlB . I tried this, but I got this error:

 

"Failed to resolve name 'Base_'. It is not a valid table, variable, or function name."

 

 

Super User
Super User

Re: Payroll Number Semi-Monthly

Try this

NewCol =
VAR Base_ = (2 * ( MONTH ( CalendarTable[Date] ) - 1 )) + 1
RETURN
SWITCH (TRUE ();
DAY ( CalendarTable[Date] ) < 16; Base_;
Base_ + 1
)

View solution in original post

bice_cold Regular Visitor
Regular Visitor

Re: Payroll Number Semi-Monthly

This worked, thanks so much! I've never come across something like this before. It's a really interesting and creative solution.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)