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 tried so far has failed, Any suggestions?

 

 

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
    )

4 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    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
        Icon for Microsoft Employee rankMicrosoft 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