cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JarekkWr
Helper I
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).

1.PNG

Any ideas?

BR,
Jarek

3 ACCEPTED SOLUTIONS
RicoZhou
Community Support
Community Support

Hi @JarekkWr 

In addition to 

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.

2.png

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. 

View solution in original post

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

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:

1.png

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.

3.png

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. 

View solution in original post

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

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:

1.png

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. 

View solution in original post

11 REPLIES 11
JarekkWr
Helper I
Helper I

@RicoZhou thanks for help and many patience!

JarekkWr
Helper I
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

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

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:

1.png

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. 

View solution in original post

JarekkWr
Helper I
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

JarekkWr
Helper I
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:

 

1.PNG

 

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

Thanks in advance for any hints and help! 🙂

BR

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

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

New Data model:

1.png

Result:

2.png

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. 

Hi @RicoZhou 

 

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

1.PNG

 

Do you have any ideas?

 

Thanks and br,

Jarek

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

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:

1.png

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.

3.png

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. 

View solution in original post

RicoZhou
Community Support
Community Support

Hi @JarekkWr 

In addition to 

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.

2.png

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. 

View solution in original post

ryan_mayu
Super User II
Super User II

@JarekkWr 

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

1.PNG

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

1.PNG

Hope this is helpful.





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

Proud to be a Super User!




S_JB
Resolver III
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]))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors