Solved: Week Ending Date in Power query editor using custo...

vjnvinod · ‎12-05-2020

Hi All,

Currently i am using the below formula in excel to get my week ending date

=[@[Transaction Date]]+7-WEEKDAY([@[Transaction Date]],16)

How do i get this logic in power query editior using custom coloumn?

i am aware i can create a "calculated coloumn" but for some reason i want to do this calculation in power query editor?

Let me know if you can help on this

mahoneypat · ‎12-05-2020

In the code for the step you added for end of week, just add Day.Saturday like this

= Table.AddColumn(#"Inserted Quarter", "End of Week", each Date.EndOfWeek([Date], Day.Friday), type date)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

mahoneypat · ‎12-05-2020

In the code for the step you added for end of week, just add Day.Saturday like this

= Table.AddColumn(#"Inserted Quarter", "End of Week", each Date.EndOfWeek([Date], Day.Friday), type date)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

CNENFRNL · ‎12-05-2020

Hi, @vjnvinod , it's even easier in Power Query; a prêt-à-manger functionality is available on UI.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!