Payroll Dates

[mdaamirkhan]

Hi All

I need help I have PayDate but how will I create other two column as per below: If you can provide me with dax.

[v-alq-msft · Accepted solution]

Hi, @mdaamirkhan 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = 
CALENDAR(
    DATE(2019,1,1),
    DATE(2020,12,31)
)

Year Month(a calculated column):

YearMonth = YEAR([Date])*100+MONTH([Date])

You may create two calculated columns as below.

ResultColumn1 = 
var _lastdate = 
CALCULATE(
    MAX('Table'[PayDate]),
    FILTER(
        'Table',
        'Table'[PayDate]<EARLIER('Table'[PayDate])
    )
)
var _lastyearmonth = 
CALCULATE(
    MAX('Calendar'[Date]),
    FILTER(
        'Calendar',
        'Calendar'[YearMonth]=
        CALCULATE(
            MAX('Calendar'[YearMonth]),
            FILTER(
                'Calendar',
                [YearMonth]<YEAR([PayDate])*100+MONTH([PayDate])
            )
        )
    )
)
return
IF(
    ISBLANK(_lastdate),
    FORMAT(_lastyearmonth,"mmmm"),
    FORMAT(_lastdate,"mmmm")
)

ResultColumn2 = 
var _nextdate = 
CALCULATE(
    MIN('Table'[PayDate]),
    FILTER(
        'Table',
        [PayDate]>EARLIER('Table'[PayDate])
    )
)
return
IF(
    ISBLANK(_nextdate),
    MONTH([PayDate])+1,
    MONTH(_nextdate)
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mdaamirkhan , what is the logic behind it ?

Assuming the date is 10th, create follwing columns

Month Date= If(day([payDate]) <=10 , eomonth([payDate],-1), eomonth([payDate],0))

Month Name = format([Month Date], "MMMM")

Count = countx(filter(table, [Month Date] = earlier([Month Date])),[payDate])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.