Forum Discussion

bansheeboyb's avatar
bansheeboyb
Regular Visitor
6 years ago
Solved

Combining weekend margin with Friday.

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 tri...
Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    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's avatar
bansheeboyb
Regular Visitor
6 years ago

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's avatar
DataZoe
Microsoft Employee
6 years ago

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's avatar
    bansheeboyb
    Regular Visitor
    6 years ago

    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!