Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
raassd
Helper I
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.

DateWeekDayWeeknuberSalesResult
26-09-2022Monday402020
27-09-2022Tuesday403030
28-09-2022Wednesday403030
29-09-2022Thursday404040
30-09-2022Friday405059
01-10-2022Saturday405 
02-10-2022Sunday414 
03-10-2022Monday415050
04-10-2022Tuesday416060
05-10-2022Wednesday414040
06-10-2022Thursday413038
07-10-2022Friday41  
08-10-2022Saturday413 
09-10-2022Sunday425 

 

thanks in advance !

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
johnt75
Super User
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
)
Greg_Deckler
Super User
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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.