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 (e.g. Productivity) reports weekly. So SalaryHours[Weeknum] increments by 2, while Productivity[Weeknum] increments by 1.

 

I would like to smooth out the SalaryHours data by taking SalaryHours[Hours] and dividing by 2 across the correct SalaryHours[Weeknum] and the Weeknum 1 below the correct SalaryHours[Weeknum]. This would give me a full set of Hours by Weeknum incrementing by 1, which could match to Productivity data better.

 

I know that in Excel, I could accomplish what I'm looking through through an IFERROR + VLOOKUP combination where I would compare the Productivity[Weeknum] to SalaryHours[Weeknum], checking for Weeknum+1 upon an error, and halving the result. What would the equivalent be in DAX?

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

3 Replies

  • 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.

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

    Hi, gsc-nvr ;

    I think it is better to use DAX in PowerBI, since you are not very clear about your data and the results you want to output, can you share simple data and the results you want to output after removing sensitive information?

  • gsc-nvr's avatar
    gsc-nvr
    Frequent Visitor
    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)
)