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

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

Hi Tom, I really appreciate the assist!  Share with mailer@lithium.com or?

Highlighted
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

 

https://drive.google.com/drive/folders/1G4LvM8r1FNLxjtg8lNxMZYauwhWwqwYN?usp=sharing

Super User
Super User

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

Hey,

 

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.

image.png

 

This formula flags the most recent weekend

if
List.Contains({4,5},
Duration.Days(
#date(
Date.Year(DateTime.LocalNow())
,Date.Month(DateTime.LocalNow()),
Date.Day(DateTime.LocalNow())) - [Date])) 
then "yes" else "no"

Here is a little screenshot:

image.png

 

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

 

Hopefully this gets you started

 

Regards,

Tom

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

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

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

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.

Users Online
Currently online: 160 members 1,700 guests
Please welcome our newest community members: