Forum Discussion

BrettM's avatar
BrettM
Frequent Visitor
2 years ago
Solved

Last Week Total in If(IsBlank)

I am looking to add a Measure that will result in these same values for a week prior. Then with these results I am looking for a suggested visual that will show whether it is up or down from week over week. 

 

TotalBoxesShipped =
IF(ISBLANK(CALCULATE
    (SUM('vw_PBI_ShipmentsReturns'[Total Boxes Shipped/Returned]),
    'vw_PBI_ShipmentsReturns'[Status] IN {"Ship"})),0,
    CALCULATE(SUM('vw_PBI_ShipmentsReturns'[Total Boxes Shipped/Returned]),
    'vw_PBI_ShipmentsReturns'[Status] IN { "Ship" }
    ))
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi BrettM ,

     

    According to the formula you provided, it should be calculating the total number of boxes shipped in the view ('vw_PBI_ShipmentsReturns') with a specific status of 'Ship'.

    Last week's total shipments are referenced in the following formula:

    TotalBoxesShippedWeekPrior = 
CALCULATE(
    [TotalBoxesShipped],
    DATEADD('Date'[Date], -7, DAY) 
)

    The week to week increase or decrease is referenced in the formula below:

    UpDownWeekOverWeek = 
VAR CurrentWeek = [TotalBoxesShipped]
VAR WeekPrior = [TotalBoxesShippedWeekPrior]
RETURN
    IF (
        ISBLANK ( CurrentWeek ) || ISBLANK ( WeekPrior ),
        BLANK (),
        IF ( CurrentWeek > WeekPrior, "Up", "Down" )
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi BrettM ,

     

    According to the formula you provided, it should be calculating the total number of boxes shipped in the view ('vw_PBI_ShipmentsReturns') with a specific status of 'Ship'.

    Last week's total shipments are referenced in the following formula:

    TotalBoxesShippedWeekPrior = 
CALCULATE(
    [TotalBoxesShipped],
    DATEADD('Date'[Date], -7, DAY) 
)

    The week to week increase or decrease is referenced in the formula below:

    UpDownWeekOverWeek = 
VAR CurrentWeek = [TotalBoxesShipped]
VAR WeekPrior = [TotalBoxesShippedWeekPrior]
RETURN
    IF (
        ISBLANK ( CurrentWeek ) || ISBLANK ( WeekPrior ),
        BLANK (),
        IF ( CurrentWeek > WeekPrior, "Up", "Down" )
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly