The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello, So I have this currently and it does not quite fit with what I need. As you can see, the Monday Start Calendar correctly ties the whole week to monday's date as shown below. Also the code for the Monday Start Calendar is this,
However, I need it to be a little more dynamic. Lets say I have a promotion that starts on a wednesday, instead of the week starting to show a monday start, I need it to show a Wednesday start and the next 6 days after also have the same date as the column above does with a monday start. Or if it starts on a Saturday, I need the week starting to be that Saturday date. Any ideas on a calculation to make this happen? Let me know if I need to explain something further.
Solved! Go to Solution.
Thanks for the reply from @ryan_mayu and @Shashankgodala , please allow me to provide another insight:
Hi @Brady-Linnebur ,
Here are the steps you can follow:
1. Create calculated column.
Step1 =
var _mindate=
MINX(
FILTER(ALL('Table'),'Table'[Event]=EARLIER('Table'[Event])),[Date])
var _mindateweekday=
WEEKDAY(_mindate,2)
var _test=
IF(
WEEKDAY('Table'[Date],2)=_mindateweekday,'Table'[Date],BLANK())
RETURN
_test
Step2 =
IF(
[Step1]=BLANK(),0,1)
Group =
SUMX(
FILTER(ALL('Table'),
'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Date]<=EARLIER('Table'[Date])),[Step2])
Step3 =
IF(
[Step1]=BLANK(),
MINX(
FILTER(ALL('Table'),
'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Group]=EARLIER('Table'[Group])&&
'Table'[Date]<EARLIER('Table'[Date])&&[Step1]<>BLANK()),[Step1]),[Step1])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply from @ryan_mayu and @Shashankgodala , please allow me to provide another insight:
Hi @Brady-Linnebur ,
Here are the steps you can follow:
1. Create calculated column.
Step1 =
var _mindate=
MINX(
FILTER(ALL('Table'),'Table'[Event]=EARLIER('Table'[Event])),[Date])
var _mindateweekday=
WEEKDAY(_mindate,2)
var _test=
IF(
WEEKDAY('Table'[Date],2)=_mindateweekday,'Table'[Date],BLANK())
RETURN
_test
Step2 =
IF(
[Step1]=BLANK(),0,1)
Group =
SUMX(
FILTER(ALL('Table'),
'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Date]<=EARLIER('Table'[Date])),[Step2])
Step3 =
IF(
[Step1]=BLANK(),
MINX(
FILTER(ALL('Table'),
'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Group]=EARLIER('Table'[Group])&&
'Table'[Date]<EARLIER('Table'[Date])&&[Step1]<>BLANK()),[Step1]),[Step1])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I would also add I have promotions going on at the same time, not sure if that would change anything. Any ideas on why it didn't do the correct output?
Hello,
From the data you have shown, I am assuming all the data is related to one promotion.
Follow the steps below in your power query:
1. Create a new column that would have a unique week number based on Year and WeekOfYear.
= Table.AddColumn(#"Changed Type1", "Week of Year", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.WeekOfYear([Date])))
2. Group the data on Week of Year column and get the earliest date for each week.
= Table.Group(#"Added Custom", {"Week of Year"}, {{"Earliest Date", each List.Min([Date]), type nullable date}})
3. Create a nested join between the resulting tables of the above 2 queries on the Week of Year column and expand the earliest Date column from Grouped Table.
= Table.NestedJoin(#"Added Custom", {"Week of Year"}, #"Grouped Rows", {"Week of Year"}, "Added Custom", JoinKind.LeftOuter)
= Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Earliest Date"}, {"Added Custom.Earliest Date"})
I hope this helps 😊
I will have more than 1 promotion, and sometimes more than 1 promotion happening at one time.
could you pls provide the expected output based on the sample data you provided?
i think you have more than one promotion? What i think is to display the promotion name instead of weekday name
Proud to be a Super User!
I do have more than 1 event going on. Below is what I would expect. Based on whatever the first Date of the event is, that would be the week starting.
how you get the event info? there is something missing in your sample data because we can't do the data transfrom from your sample data to this output.
Proud to be a Super User!
It is in a promotion table. The table is connected to the week starting table by date.