cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Irussaa Regular Visitor
Regular Visitor

ASAP - Need formula

Hi guys,

 

Anyone have formulation to generate weekending data like the picture below (rounded in red line)?

I need colum of data that contain week ending date with format just like in the pict attached.

Inked01_LI.jpg

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: ASAP - Need formula

Hi @Irussaa ,

 

If you want a DAX formula, you could try the following DAX:

Column =
"W" & WEEKNUM ( 'DateDim'[WeekEnding], 1 ) & " "
    & MONTH ( 'DateDim'[WeekEnding] ) & "/"
    & DAY ( 'DateDim'[WeekEnding] )

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

10 REPLIES 10
Nathaniel_C Super Contributor
Super Contributor

Re: ASAP - Need formula

Hi @Irussaa 

 

ColumnName = 

Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day 
 (Date.EndOfWeek([date]))})

 

Tell me if this works, since I did not have your table, I did this free hand. This is in Power Query.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 

Irussaa Regular Visitor
Regular Visitor

Re: ASAP - Need formula

Hi @Nathaniel_C 

 

I try to apply 

ColumnName = 

Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day 
 (Date.EndOfWeek([date]))})

 

Inside my data set:

 

WE = Text.Combine({[DayofWeek]," ",Number.ToText([MonthofYear]),"/", Number.ToText(DateDim[Day]
(Date.EndOfWeek([Date]))})
 
003.PNG
 
But its Error.
 
Can you explain to me what is 
[Week]
[MonthN]
Date.Day
(Date.EndOfWeek([date])
Nathaniel_C Super Contributor
Super Contributor

Re: ASAP - Need formula

Hello @Irussaa ,
Here is a link to Power Query Power Query . It is the best place to add Calculated Columns.

However this is a way that you can do it in Power BI. 

WE= table[Week] && " " && table[MonthN] && "/" && Day(table[Date]+ 7-WEEKDAY(table[Date],1))

In your post you show a table with columns. I took the name of the columns from that picture. You will want to replace "table" in my formula with your table name. 
weekend.PNG

 

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Community Support Team
Community Support Team

Re: ASAP - Need formula

Hi @Irussaa ,

 

If you want a DAX formula, you could try the following DAX:

Column =
"W" & WEEKNUM ( 'DateDim'[WeekEnding], 1 ) & " "
    & MONTH ( 'DateDim'[WeekEnding] ) & "/"
    & DAY ( 'DateDim'[WeekEnding] )

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Irussaa Regular Visitor
Regular Visitor

Re: ASAP - Need formula

It works!  @v-eachen-msft 

 

But I have one more question, why is that first week ending in the beginning of the month (which is the first week in january) already generate WE2 not WE1? 

 

InkedWE OK_LI.jpg

 

Thank you

Community Support Team
Community Support Team

Re: ASAP - Need formula

Hi @Irussaa ,

 

Replace WEEKNUM ( 'DateDim'[WeekEnding], 1 ) with WEEKNUM ( 'DateDim'[WeekEnding], 2 ). After my tests, it will work.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Irussaa Regular Visitor
Regular Visitor

Re: ASAP - Need formula

@v-eachen-msft thank you so much!

Irussaa Regular Visitor
Regular Visitor

Re: ASAP - Need formula

@v-eachen-msft I'm sorry can I ask one more question?

 

Why the WE data isn't arranged in the chart?

 

WE NOT ARRANGED.PNG

 

Thank you!

Super User
Super User

Re: ASAP - Need formula

@Irussaa Please change your WE column "Sort By Column" to the WeekEndingNumber that needs to be derived from WeekEndingDate. (As you can't directly use the WeekEndingDate for Sort By Column, as WE is using that field)



Did I answer your question? Mark my post as a solution !

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)