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.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)