Forum Discussion

mdaamirkhan's avatar
mdaamirkhan
Post Prodigy
5 years ago
Solved

Payroll Dates

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.

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.