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?

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 140 members 1,857 guests
Please welcome our newest community members: