cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Sat Sunday value to get added to Friday

Hi Team,

I am trying to get this output in PowerBI.

My requirment is what ever the sale value we get it on Saturday and Sunday I will add that to my previous value and Sat and Sunday will be shown as blank. How to achieve this.

 Date WeekDay Weeknuber Sales Result 26-09-2022 Monday 40 20 20 27-09-2022 Tuesday 40 30 30 28-09-2022 Wednesday 40 30 30 29-09-2022 Thursday 40 40 40 30-09-2022 Friday 40 50 59 01-10-2022 Saturday 40 5 02-10-2022 Sunday 41 4 03-10-2022 Monday 41 50 50 04-10-2022 Tuesday 41 60 60 05-10-2022 Wednesday 41 40 40 06-10-2022 Thursday 41 30 38 07-10-2022 Friday 41 08-10-2022 Saturday 41 3 09-10-2022 Sunday 42 5

1 ACCEPTED SOLUTION
Super User

@raassd PBIX is attached below signature. Maybe:

``````Result Column =
VAR __Date = [Date]
VAR __Weeknum = [Weeknuber]
VAR __Weekday = WEEKDAY(__Date,2)
VAR __Sales =
SWITCH(TRUE(),
__Weekday > 5,BLANK(),
( __Weekday = 5 && [Sales] <> BLANK() ) ||
(__Weekday = 4 && ISBLANK(SUMX(FILTER('Table',[Weeknuber] = __Weeknum && WEEKDAY([Date],2) = 5),[Sales])) ), SUMX(FILTER('Table',[Weeknuber] = __Weeknum && WEEKDAY([Date],2) >= __Weekday && WEEKDAY([Date],2)<>7),[Sales]) + SUMX(FILTER('Table',[Weeknuber] = __Weeknum+1 && WEEKDAY([Date],2) = 7),[Sales]),
[Sales]
)
RETURN
__Sales``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

You can use

``````Sales Measure =
SUMX(
'Table',
VAR CurrentDate = 'Table'[Date]
VAR NextMonday = CurrentDate - WEEKDAY( CurrentDate, 2 ) + 8
VAR NextSaturday = CurrentDate - WEEKDAY( CurrentDate, 2 ) + 6
VAR TotalAfterToday =
CALCULATE( SUM( 'Table'[Sales] ), REMOVEFILTERS( 'Table' ), 'Date'[Date] > CurrentDate && 'Date'[Date] < NextMonday )
VAR TotalWeekendSales =
CALCULATE( SUM( 'Table'[Sales] ), REMOVEFILTERS( 'Table' ), 'Date'[Date] >= NextSaturday && 'Date'[Date] < NextMonday )
VAR Result =
IF(
WEEKDAY( CurrentDate, 1 ) IN { 1, 7 } || ISBLANK( 'Table'[Sales] ),
BLANK( ),
IF( TotalAfterToday = TotalWeekendSales, TotalAfterToday + 'Table'[Sales], 'Table'[Sales] )
)
RETURN
Result
)
``````
Super User

@raassd PBIX is attached below signature. Maybe:

``````Result Column =
VAR __Date = [Date]
VAR __Weeknum = [Weeknuber]
VAR __Weekday = WEEKDAY(__Date,2)
VAR __Sales =
SWITCH(TRUE(),
__Weekday > 5,BLANK(),
( __Weekday = 5 && [Sales] <> BLANK() ) ||
(__Weekday = 4 && ISBLANK(SUMX(FILTER('Table',[Weeknuber] = __Weeknum && WEEKDAY([Date],2) = 5),[Sales])) ), SUMX(FILTER('Table',[Weeknuber] = __Weeknum && WEEKDAY([Date],2) >= __Weekday && WEEKDAY([Date],2)<>7),[Sales]) + SUMX(FILTER('Table',[Weeknuber] = __Weeknum+1 && WEEKDAY([Date],2) = 7),[Sales]),
[Sales]
)
RETURN
__Sales``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors