Daily data to bi-weekly with a "seed" date

[tunneling]

We produce reporting on a bi-weekly basis based on a reporting calendar. I am trying to generate a calculated column to represent the Period Ending Date. Our weeks end on a Sunday and our data is seperated to daily when it's brought into Power BI.

Using a "seed" date of 08/02/2020 (which aligns with our calendar), I have successfully created this Period Ending Date field using the following in SQL:

dateadd(day, ceiling(datediff(day,'8/2/2020', daily_date) / 14.0) * 14, '8/2/2020') As period_end_date, 

However, when I try a simliar function to create a New Column in Power BI, the results are either blank or just wrong:

Period Ending Date = DATEADD('pcwb budget daily'[08022020], (CEILING(DATEDIFF(DATE(2020,8,2),'pcwb budget daily'[daily_date],DAY) / 14,1) * 14),DAY)

NOTE: Since the Dax DATEADD will not allow a constant as the first parameter, I created a column to hold the "seed" date. That column is named 'pcwb budget daily'[08022020]. For all rows, it's value is 8/2/2020.

I don't know if I'm dealing with a data type issue or if I'm just misunderstanding the Dax functions (e.g. expecting them to work like SQL's).

[amitchandak]

@tunneling , Try a new column like

'pcwb budget daily'[daily_date] + mod(datediff(DATE(2020,8,2), 'pcwb budget daily'[daily_date],DAY),14) *14

or

'pcwb budget daily'[daily_date] + (mod(datediff(DATE(2020,8,2), 'pcwb budget daily'[daily_date],DAY),14)+1) *14

[tunneling · Accepted solution]

Your response at least got me in the right direction. Here is the solution:

Period Ending Date = 'pcwb budget daily'[daily_date]  + mod(datediff('pcwb budget daily'[daily_date],DATE(2020,8,2),DAY), 14)