cancel
Showing results for
Did you mean:
Helper I

## Weekend values on Monday

Hi,

I checked other topics but without success. I need to add weekend values (sobota - Saturday and niedziela - Sunday) on Mondays (poniedzialek).

Any ideas?

BR,
Jarek

3 ACCEPTED SOLUTIONS
Community Support

``````Weekend values on Monday =
IF (
HASONEVALUE ( 'Table'[Date] ),
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
),
'Table'[Total _Orders]
),
SUM ( 'Table'[Total _Orders] )
)
),
SUM ( 'Table'[Total _Orders] )
)``````

Result is as below.

You can download the pbix file from this link: Weekend values on Monday

Best Regards,

Rico Zhou

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

Community Support

Due to your first visual is table visual and second is matrix visual, so you can build a new measure for matirx visual.

My new sample table:

Measures:

``````Table.Monday value Measure =
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
),
'Table'[Total_Orders]
),
SUM ( 'Table'[Total_Orders] )
)
)``````
``````Table.Weekend values on Monday =
IF (
HASONEVALUE ( 'Table'[Date] ),
'Table'[Table.Monday value Measure],
SUMX (
SUMMARIZE ( 'Table', 'Table'[Date], "Value", [Table.Monday value Measure] ),
[Value]
)
)``````
``````Matrix.Monday value Measure =
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
&&'Table'[User] = MAX('Table'[User])
),
'Table'[Total_Orders]
),
SUM ( 'Table'[Total_Orders] )
)
)``````
``Matrix.Weekend values on Monday = SUMX('Table',[Matrix.Monday value Measure])``

Result is as below.

You can download the pbix file from this link: Weekend values on Monday

Best Regards,

Rico Zhou

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

Community Support

I update your Matrix.Weekend values on Monday Measure.

``````Matrix.Weekend values on Monday =
VAR _T = SUMMARIZE('Table','Table'[User],'Table'[Date],"Value",[Matrix.Monday value Measure])
Return
SUMX(_T,[Value])``````

Result is as below:

Best Regards,

Rico Zhou

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

11 REPLIES 11
Helper I

@RicoZhou thanks for help and many patience!

Helper I

Hi @RicoZhou ,

I modified your sample data to reflect my real life dataset more and unfortunately I'm still struggling with wrong values on Mondays in Matrix visualisations.

Changes I made :

- I added more days to have longer period to check,
- added "team" table,
- set relationship between "team" and "table" with values.

Could you please take a look at sample pbix? Click to download

Thanks again!

Jarek

Community Support

I update your Matrix.Weekend values on Monday Measure.

``````Matrix.Weekend values on Monday =
VAR _T = SUMMARIZE('Table','Table'[User],'Table'[Date],"Value",[Matrix.Monday value Measure])
Return
SUMX(_T,[Value])``````

Result is as below:

Best Regards,

Rico Zhou

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

Helper I

Hi @RicoZhou!

You're brilliant, your both solutions are working!

Is there a way to little speed up performance as I have lots of data and it's taking quite a time to digest it in Power BI? Maybe it's because of sumx used here?

`Matrix.Weekend values on Monday = SUMX('Table',[Matrix.Monday value Measure])`

Sorry for many questions but I'm a newbie in Power BI and I really want to understand logic of it. I read that CALCULATE is more efficient than SUMX but I tried different ways to modify this measure and without success 😞

Best regards,

Jarek

Helper I

Hello,

unfortunately none of your solution is working because I'm getting wrong value on Monday (I get 3505 but it should be 1274) . Results for Tuesday-Friday and Total are correct. Below I'm using RicoZhou solution:

Maybe the problem is because of why my raw data is constructed?

Thanks in advance for any hints and help! 🙂

BR

Community Support

I update my data model, and my measure works well.

New Data model:

Result:

value in 2020/11/9 is equal to 2020/11/07+2020/11+08+2020/11/09: (288+1)+(37+1)+(4747+1) = 5075.

Could you show me the sum of values in 2020/11/07, 2020/11/08 and 2020/11/09 ?

Is your calculate logic : 2020/11/09 = 2020/11/07+2020/11/08+2020/11/09 = 1274?

Best Regards,

Rico Zhou

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

Helper I

Your solution is working indeed, big thanks for your patience!

However, I have another problem because when I try to apply filter and check how many orders were processed in selected week by respective team I get results as follows (problem with Monday):

Do you have any ideas?

Thanks and br,

Jarek

Community Support

Due to your first visual is table visual and second is matrix visual, so you can build a new measure for matirx visual.

My new sample table:

Measures:

``````Table.Monday value Measure =
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
),
'Table'[Total_Orders]
),
SUM ( 'Table'[Total_Orders] )
)
)``````
``````Table.Weekend values on Monday =
IF (
HASONEVALUE ( 'Table'[Date] ),
'Table'[Table.Monday value Measure],
SUMX (
SUMMARIZE ( 'Table', 'Table'[Date], "Value", [Table.Monday value Measure] ),
[Value]
)
)``````
``````Matrix.Monday value Measure =
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
&&'Table'[User] = MAX('Table'[User])
),
'Table'[Total_Orders]
),
SUM ( 'Table'[Total_Orders] )
)
)``````
``Matrix.Weekend values on Monday = SUMX('Table',[Matrix.Monday value Measure])``

Result is as below.

You can download the pbix file from this link: Weekend values on Monday

Best Regards,

Rico Zhou

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

Community Support

``````Weekend values on Monday =
IF (
HASONEVALUE ( 'Table'[Date] ),
IF (
MAX ( 'Table'[WeekDay] ) IN { 5, 6 },
BLANK (),
IF (
MAX ( 'Table'[WeekDay] ) = 0,
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= MAX ( 'Table'[Date] ) - 2
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
),
'Table'[Total _Orders]
),
SUM ( 'Table'[Total _Orders] )
)
),
SUM ( 'Table'[Total _Orders] )
)``````

Result is as below.

You can download the pbix file from this link: Weekend values on Monday

Best Regards,

Rico Zhou

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

Super User II

I am not sure if the sample data fits your real business.

you can create two measures

``````Measure =
VAR _day=max('Table'[date])
return  if(WEEKDAY(_day,2) in {6,7},0,if(WEEKDAY(_day,2)=1,CALCULATE(sum('Table'[amount ]),FILTER(all('Table'),'Table'[date]>=_day-2&&'Table'[date]<=_day)),sum('Table'[amount ])
))

Measure 2 = sumx('Table',[Measure])``````

Hope this is helpful.

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

Proud to be a Super User!

Resolver III

You could create a new column using an IF statement to do this. Please see the below logic:

Weekday =
IF(Query1[Dzien Tygodnia] = 5,
0,
IF(Query1[Dzien Tygodnia]=6,
0,
Query1[Dzien Tygodnia]))

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors