Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tunneling
New Member

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

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

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.