Combining weekend margin with Friday.

[bansheeboyb]

I am trying to get Saturday and Sundays margin to be added into the previous Friday.

It is a very simple table and everything.  I have the ship date, margin, and day number. Everything I have tried so far has failed, Any suggestions?

[DataZoe · Accepted solution]

Hi, this measure should give you what you want, the hasonevalue will also make it so it totals correctly too.

Margin Roll Into Friday =

VAR _margin =

SUM ( 'RollIntoFriday'[Margin] )

RETURN

IF (

HASONEVALUE ( RollIntoFriday[Ship Date] ),

SUMX (

VALUES ( 'RollIntoFriday'[Ship Date] ),

IF (

WEEKDAY ( 'RollIntoFriday'[Ship Date] ) = 6,

_margin

+ CALCULATE (

SUM ( 'RollIntoFriday'[Margin] ),

NEXTDAY ( RollIntoFriday[Ship Date] )

)

+ CALCULATE (

SUM ( 'RollIntoFriday'[Margin] ),

NEXTDAY ( NEXTDAY ( RollIntoFriday[Ship Date] ) )

),

IF ( WEEKDAY ( 'RollIntoFriday'[Ship Date] ) IN { 1, 7 }, BLANK (), _margin )

)

),

_margin

)

[bansheeboyb]

Hey, Thanks for trying to help me out with this.

I tried that measure, but it doesnt seem to work. Here is an iamge that shows what I get from it. 

Any ideas?

[DataZoe]

From what you showed, it looks like the nextday() is not working properly. (https://docs.microsoft.com/en-us/dax/nextday-function-dax ).

Is ship date set as a date column? If it's in a dim date table, also make sure the table is set as date table. If it's not in a dim date table, the auto date/time intelligence should be on (it's on by default). Also make sure your power bi desktop is on the latest version (Version: 2.79.5768.1082 64-bit (March 2020)) just to cover all the bases!

I was able to get it to work with [Ship Date] being a date column:

I also included PBIX file here: https://github.com/DataZoe/PBIX/blob/master/RollIntoFridayExample.pbix 

[bansheeboyb]

Sweet, instead of using the ship date in the query that was pulling the information, I used the master date dimension table I created to tie all my queries together and now it works perfectly.

Thanks again!