Weekend values on Monday

[JarekkWr]

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

[v-rzhou-msft · Accepted solution]

Hi @JarekkWr 

In addition to ryan_mayu 's reply, you can try hasonevalue function to show total value in resultfalse.

My Sample Table:

Measure:

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. 

[v-rzhou-msft · Accepted solution]

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:

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. 

[JarekkWr]

@v-rzhou-msft thanks for help and many patience!

[JarekkWr]

Hi @v-rzhou-msft ,

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

[v-rzhou-msft · Accepted solution]

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:

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. 

[JarekkWr]

Hi @v-rzhou-msft!

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]

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

[v-rzhou-msft]

Hi @JarekkWr 

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. 

[JarekkWr]

Hi @v-rzhou-msft 

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

[v-rzhou-msft · Accepted solution]

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:

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. 

[v-rzhou-msft · Accepted solution]

Hi @JarekkWr 

In addition to ryan_mayu 's reply, you can try hasonevalue function to show total value in resultfalse.

My Sample Table:

Measure:

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. 

[ryan_mayu]

@JarekkWr 

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.

[S_JB]

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