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

Reply
Syndicate_Admin
Administrator
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
v-linyulu-msft
Community Support
Community Support

Hi,@Syndicate_Admin 


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

 

Best Regards,

Leroy Lu

Syndicate_Admin
Administrator
Administrator

It would look something like this:

DateDiaSemanaWeekHasMonday
26/04/2024Friday17NO
27/04/2024Saturday17NO
28/04/2024Sunday17NO
29/04/2024Monday18YES
30/04/2024Tuesday18YES
01/05/2024Wednesday18YES
02/05/2024Thursday18YES
03/05/2024Friday18YES
04/05/2024Saturday18YES
05/05/2024Sunday18YES
06/05/2024Monday19YES
07/05/2024Tuesday19YES
08/05/2024Wednesday19YES
09/05/2024Thursday19YES
10/05/2024Friday19YES

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:

Hi,@Syndicate_Admin 

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:

vlinyulumsft_0-1715680045936.png

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")

 

vlinyulumsft_1-1715680097357.png

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

 

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

 

vlinyulumsft_0-1715680163095.png

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.

vlinyulumsft_0-1715680284682.png

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.

 

Something I must not be doing right:

MartinezRu_0-1715769107036.png

Hi,@Syndicate_Admin 

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.

vlinyulumsft_0-1715822791827.png

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:

vlinyulumsft_1-1715822806571.png

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.

vlinyulumsft_2-1715822820492.png

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.

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

MartinezRu_0-1715847974191.png

Hi,@Syndicate_Admin 

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:

vlinyulumsft_0-1715916606725.png

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.

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

MartinezRu_0-1715950504282.png

MartinezRu_1-1715950532265.png

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:

MartinezRu_3-1715950691222.png

Hi,@Syndicate_Admin 

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:

vlinyulumsft_0-1716183959692.png

 

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.

Syndicate_Admin
Administrator
Administrator

Thank you for asking your question

Ritaf1983
Super User
Super User

Hi @Syndicate_Admin 

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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