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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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