Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

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

Need Help

Tips and Tricks

Anonymous

5 years ago

Hi Anonymous

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.

Anonymous

5 years ago

Hi Anonymous

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.

Anonymous
5 years ago
Hi Anonymous

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

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

ryan_mayu

Super User

5 years ago

Anonymous

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.

Anonymous

Not applicable

5 years ago

Hi Anonymous

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.

Anonymous
Not applicable
5 years ago
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
- Anonymous
  Not applicable
  5 years ago
  Hi Anonymous
  
  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.
  - Anonymous
    Not applicable
    5 years ago
    Hi Anonymous
    
    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
Anonymous
Not applicable
5 years ago
Hi Anonymous!

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
Anonymous
Not applicable
5 years ago
Hi Anonymous ,

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
- Anonymous
  Not applicable
  5 years ago
  Hi Anonymous
  
  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.
Anonymous
Not applicable
5 years ago
Anonymous thanks for help and many patience!

Forum Discussion

Weekend values on Monday

11 Replies

Related content

Sum weekend values on monday

Calculate/sum up values from Weekend-days to next monday after weekend

DATEDIFF excluding weekends

Week start day monday

8 day week, Monday to Monday