Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
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
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.
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
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
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.
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
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.
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.
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.
Proud to be a Super User!
You could create a new column using an IF statement to do this. Please see the below logic:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |