last period value not calculating correctly when rolled-up

[Anonymous]

Hi all,

In the table below i have the total closed volume by day, and the volume Mvmt is the difference in volume compared to the previouse day.

it works fine on a daily level, however when rolled up into a weekly level the result is wrong. for exmaple i expect the difference between the weeks starting on 20/07/2023 and 13/07/2023 to be -11143.

how can i write my [Volume mvmt] measure in a way that would allow produce the correct result when rolled up.

Thanks

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

I modify the measure of Volume Mvmt, the function is showed below: 

Volume Mvmt = 
IF(
    ISINSCOPE('Table'[Date]) = TRUE(),
    SUM('Table'[Closed Volume]) - CALCULATE(SUM('Table'[Closed Volume]),FILTER(ALLSELECTED('Table'),[Date]=MAX([Date])-1)),
    IF(
        ISINSCOPE('Table'[WeekStartThursday]) = TRUE(),
        CALCULATE(SUM('Table'[Closed Volume]),FILTER(ALLSELECTED('Table'),[WeekStartThursday]=MAX('Table'[WeekStartThursday])+7)) - SUM('Table'[Closed Volume])
    )
)

Description of the code:
            The two if statements help us to distinguish two different levels of the table.

            ISINSCOPE('Table'[Date]) = TRUE() and  ISINSCOPE('Table'[WeekStartThursday]) = TRUE() each is a Boolean function identifies date or week layer.

            Calculate function gives us the final result.

Here is the outcome:

Best Regards,

Stephen Tao

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

[Anonymous]

Thank you Stephen, the ISINSCOPE fucntion was exactly what i needed.