Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
XaviOV
Helper V
Helper V

Relative data begin to Monday until Sunday

Hi,


I'm working with a "Clustered column chart", and I'm filtering by "Relative filter" in this week, but the week of Power begin in Sundayand I would like that the week will begin in Monday.

How I do it?


Regards,


Xavi

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @XaviOV 

 

By the look of, this option is not yet available in Power BI, but you can vote for it here.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20248087-relative-date-filter-wee...

 

As an alternative, you can build a Relative Date Dimension as per below article where you can specify the start of the week.

https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039

 

Just adjust week to start on Monday
Date.WeekOfYear( date, Day.Monday )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

View solution in original post

10 REPLIES 10
Pragati11
Super User
Super User

Hi @XaviOV ,

 

I think I got a work-around of achieving this.

I have a sample data with 2 columns:

d1.png

 

I added two columns to this using following DAX expression:

Day name = FORMAT(Sheet1[Date], "dddd")
DayNum = WEEKDAY(Sheet1[Date],2)
 
The final data is as follows:
d2.png
 
I sorted "Day Name" column by "DayNum" column as follows:
d3.png
 
Then I created a Clustered bar chart as follows:
d4.png
Then further applied a Relative date filter to display data for last week:
d5.png
Just let me know if this is what is required.
 
If this helps appreciate a Kudos and mark it as a Solution! 🙂
 
Thanks,
Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 ,

 

Yes, but in this case the value of Monday is the value of the last week no of the current week 😞

 

Regards,

 

Xavi

Hi @XaviOV ,

 

Apologies, didn't understand your question.

You can use Relative Date filter for slicing data for current week, last week, etc.

 

If this helps appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

amitchandak
Super User
Super User

The information you have provided is not making the problem clear to me. Can you please explain with an example.

 

You can refer this file how to deal with week from Monday to Friday

https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0

Appreciate your Kudos.

Hi @amitchandak , @Mariusz and @Pragati11 

 

I attachment a picture, I'm filtering the column chart by a week ago but the week begin to Sunday until Monday, and would like that the week begin to Monday until Sunday. In the column chart appear the last Monday (Domingo in spanish)

 

Relative date.png

 

Regards,

 

Xavi

Mariusz
Community Champion
Community Champion

Hi @XaviOV 

 

As mentioned in my previous post, what you asked of can not be achieved currently with relative filters, so please refer to both links I have posted previously.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Hi @XaviOV ,

 

Is it possible for you to share this pbix file?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 ,
My company does not allow it, what do you need?

I am sorry,

Xavi

Mariusz
Community Champion
Community Champion

Hi @XaviOV 

 

By the look of, this option is not yet available in Power BI, but you can vote for it here.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20248087-relative-date-filter-wee...

 

As an alternative, you can build a Relative Date Dimension as per below article where you can specify the start of the week.

https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039

 

Just adjust week to start on Monday
Date.WeekOfYear( date, Day.Monday )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

Pragati11
Super User
Super User

Hi @XaviOV ,

 

Have you got a caluclated column for Week Number in your data? If yes, then use the "WEEKDAY" dax function with "return_type" = 2, to start your week with Monday.

Refer following for detail:

https://docs.microsoft.com/en-us/dax/weekday-function-dax

 

If this helps appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.