last period value not calculating correctly when rolled-up

Question

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.&nbsp;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&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
I modify the measure of Volume Mvmt, the function is showed below:&nbsp;
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: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The two if statements help us to distinguish two different levels of the table.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ISINSCOPE('Table'[Date]) = TRUE() and &nbsp;ISINSCOPE('Table'[WeekStartThursday]) = TRUE() each is a Boolean function identifies date or week layer.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Calculate function gives us the final result.
&nbsp;
Here is the outcome:
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

last period value not calculating correctly when rolled-up

2 Replies

Related content

Roll up Calculations

Need Roll up customization

Data not rolling up correctly with date columns

date roll up issue

Cumulative Averages by Day and rolled Up