Forum Discussion

gsc-nvr's avatar
gsc-nvr
Frequent Visitor
5 years ago
Solved

Modifying bi-weekly data to be weekly

Hi folks, brand new user of Power BI here with a question I can't search an answer for.   I have a table of salary hours which reports by payroll period (every two weeks), while all my other data (...
Need Help
Tutorial Requests
  • gsc-nvr's avatar
    gsc-nvr
    5 years ago

    Thank you lbendlin for pointing me in the direction of a date table - that helped solve part of the puzzle. Using a date table and the below measure, I was able to smooth out my salary hours across two weeks as desired.

    SalaryHours Smoothed = 
if(
    ISBLANK(SUM(SalaryHours[Hours])),
    CALCULATE(
        DIVIDE(SUM(SalaryHours[Hours]),2),
        DATEADD('Calendar'[Date],-7,day)
    ),
    DIVIDE(SUM(SalaryHours[Hours]),2)
)
lbendlin's avatar
lbendlin
Super User
5 years ago

The equivalent in DAX is to use a Calendar table with columns for dates (days) and what you consider to be weeks.  That table would tie in to both your Productivity and SalaryHours tables based on the date column and an agreed date of the week (eg beginning of the week)  and the fortnight (ie beginning of the fortnight) in your other tables.