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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Administrator

## Identify day of the week

Good morning.

I have a calendar table in which I have calculated daily sales, sales vs previous, % daily sales vs previous day, sales vs previous week, % sales vs days of the previous week and weekly cumulative in the weeks that I have day by day (if it is Monday I only have data from Monday, if it is Tuesday data from Monday + Tuesday, if it's Wednesday, Monday + Tuesday + Wednesday...).

The problem is that in that cumulative I need that if the first week that I show is not complete I don't want it to show me results, I mean, if I filter and that week does not contain Monday it should not show me anything in that week.

I understand that the easiest thing to do is to discard the week that doesn't start on a Monday on the calendar to be able to filter the sum, but I can't get it out. Any ideas?

11 REPLIES 11
Community Support

Can you tell me if your problem is solved? If yes, please accept it as solution.

Best Regards,

Leroy Lu

Administrator

It would look something like this:

 Date DiaSemana Week HasMonday 26/04/2024 Friday 17 NO 27/04/2024 Saturday 17 NO 28/04/2024 Sunday 17 NO 29/04/2024 Monday 18 YES 30/04/2024 Tuesday 18 YES 01/05/2024 Wednesday 18 YES 02/05/2024 Thursday 18 YES 03/05/2024 Friday 18 YES 04/05/2024 Saturday 18 YES 05/05/2024 Sunday 18 YES 06/05/2024 Monday 19 YES 07/05/2024 Tuesday 19 YES 08/05/2024 Wednesday 19 YES 09/05/2024 Thursday 19 YES 10/05/2024 Friday 19 YES
Community Support

Hello,@Ritaf1983 Thank you for your interest in this case. I have supplemented the data according to the user's request and realized the results. Please check if there is anything that can be improved. Below is my solution:

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.I created the following measure to count whether or not there was a Monday in that week, and the resulting graph is below:

``Monday = IF(WEEKDAY(MINX(FILTER(ALLSELECTED('Table'),'Table'[Week]=MAX('Table'[Week])),'Table'[Date]))=2 ,"YES","NO")``

3.I created the following measure to enable filtering for values that don't have a Monday:

``````Measure2 =
if ('Table'[Monday]="No",1,0)
``````

4.Regarding your desire to do a weekly day-by-day accumulation of the values for weeks with a Monday, I created the following measure:

``Measure 1 = CALCULATE(SUM('Table'[Vlaue]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Week]=MAX('Table'[Week])&&'Table'[Monday]="YES"))``

5.Here's my final result, which I hope meets your requirements.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Administrator

Something I must not be doing right:

Community Support

1.First of all, please click "Transform data" to enter the powerquery page to check whether the format of the date column you selected is Date.

2.After successful modification, I hope you can check whether the modified format is the same as what you want. Below is the screenshot of the common error report:

Powerbi generally recognizes date columns in the process, it is difficult to recognize the dd/mm/yyyyy type, so it is recommended that you change the date data to mm/dd/yyyyy or other types.

3.Here's my final result, which I hope meets your requirements.

If what you have done still does not solve the problem, you can choose to share the sensitive data deleted pbix with me, I will help you to modify. Be sure to delete sensitive information.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Administrator

In this case the calendar table is created in DAX, defined as date (short date)

Community Support

1.Below is my reproduction of your scenario, but without the problems you are experiencing:

To reproduce the problem you are having, I have also created the calculation table as shown below:

``Table = CALENDAR(TODAY()-20,TODAY())``

And using the following calculated column, I output the day of the week for that date:

``Week = WEEKNUM('Table'[Date],21)``

Secondly, to make it easier for you to see what day of the week the date is, I have also created the following calculated column:

``tod1 = FORMAT('Table'[Date],"dddd" )``

Lastly I also used the following measure to determine if there is a Monday in the week:

``Monday = IF(WEEKDAY(MINX(FILTER(ALLSELECTED('Table'),'Table'[Week]=MAX('Table'[Week])),'Table'[Date]))=2 ,"YES","NO")``

Below is a graph of my final output:

2. You can try using DAX queries on desktop to check and test your metrics or calculated columns sentence by sentence, here are the relevant links:

DAX query view in Power BI Desktop - Power BI | Microsoft Learn

My pbix file is also added as an attachment to the reply so you can check it. If there is still a problem, you can choose to send me the pbix file after removing the sensitive information (please be careful to remove the sensitive information) or please check if your desktop needs to be updated and update it promptly.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Administrator

Could it be something in the configuration of the program itself or versions? When I open your sample file, I get this error:

But it opens it up. Copying as it is to a clean GDP continues to give me the same error (it won't let me attach it).

Here's my version:

Community Support

Thanks for letting us know your user experience. According to your case description, I do understand how frustrated you are now.

Regarding the issue you raised, my solution is as follows:

1.According to the screenshot and description of your error report, your statement is correct, your requirement may indeed not be realized in your current version.You can try upgrading the version number, here is a screenshot of mine:

If you want to get the instant help, it is recommended that you can upgrade the product to the latest version.

2.This is the official download link:

Download Microsoft Power BI Desktop from Official Microsoft Download Center

If you need other help from my side, please let me know.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Administrator

Thank you for asking your question

Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors