Showing results for 
Search instead for 
Did you mean: 
Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

Hi Tom, I really appreciate the assist!  Share with or?

Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

Have the file ready to share, here is what I am trying to do and I appreciate the help I really do I have been driving myself crazy for WEEKS trying to figure this out


1.  Create dynamic grouping and filtering on the dataset called Performance_All_Dept

2.  The heirarchy (Region, Market, Store) is flat in this table.  I need to dynamically group by Heirarchy and sum the data in the CY and PY columns to then create  a YOY % of that Agg'd data by group selected.  I don't want to create slicers, rather the data be the slicer and Weekend data and MTD data on separate tabs OR

3.  My slicers need to be by Last Weekend from today (my code here works in DAX beautifully in the 'main window' but I want to build it in the query editor to eliminate all the other stuff done in the 'main window' (to me this is the presentation layer) 


IsWeekend = IF(Performance_ALL_Dept[MonthDate]= TODAY()-WEEKDAY(TODAY())+1|| Performance_ALL_Dept[MonthDate] = TODAY()-WEEKDAY(TODAY())|| Performance_ALL_Dept[MonthDate] = TODAY()-WEEKDAY(TODAY())-1,"Weekend","")



4.  My slicer also needs to slice by MTD

Super User
Super User

Re: Syntax Conflict between Advanced Editor and Custom Column Builder



unfrortunately I can't download a file from googledrive.


But nevertheless, you can use this formula to create a custom column in the Query Editor to determine if a given date is a weekend

if List.Contains({5,6},Date.DayOfWeek([Date], Day.Monday)) then "is weekend" else "no weekend"

[Date] references the date column in your table

The function Date.DayOfWeek returns values from 0 to 6, 0 is the starting day of the week. The starting day of a week is set by the 2nd parameter Day.Monday. This means that Saturday and Sunday are represented by the values 5 and 6.

The formula checks if the returned value is in the list {Day.Saturday, Day.Sunday} if true then it's a weekend otherwise not.


For the days of september the function returns the values below (assuming the week starts with a Monday.



This formula flags the most recent weekend

Date.Day(DateTime.LocalNow())) - [Date])) 
then "yes" else "no"

Here is a little screenshot:



Please be aware that the calculation will be just executed if the table will be refreshed.


Hopefully this gets you started




Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 175 members 1,945 guests
Please welcome our newest community members: