Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PowerBi Rolling Sum

Hi All,

I have a project where I need to create a rolling Sum.

I have three tables involved,  VScrapRates, VdateDim, and totalscrap.

 

My current DAX calculation is as follows:

Rolling12MonthScrap =
CALCULATE(
Sum(TotalScrap[InitalDollars]),
DATESINPERIOD( VDateDim[Date], LASTDATE( VDateDim[Date] ), -12, MONTH ) )
 
The date dim table is a date table and is tied to a timeline slicer.  When the user selects a month on the slicer we want to sum the rolling 12 months of initial dollars from the total scrap table from the selected month backwards.
 
However, this is what I'm getting.  The second row is correct and I have no idea where the first row comes from.  Ideas?
 
Thanks in advance,
K.
 
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , is one of the other your columns un summarized?

    Also, make sure yearmonth is coming from VDateDim and it is marked as date table

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , is one of the other your columns un summarized?

    Also, make sure yearmonth is coming from VDateDim and it is marked as date table

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    VDateDim is marked as a date table.  

    If I remove every column except Corporate scrap percentage from VscrapRates in the table it still happens.

    If I remove every visual filter, still happens.

    If I change the slicer attached to VdateDim, the value remains constant.

    It may be that CorporateScrapPercentage is not summarized.  Suggestions on what to try?

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, it steered me in the correct direction.  It was an issue where there was no relationship between VscrapRates[plant] and TotalScrap[Plant].

      Cleaned up relationships, modified and added some measures and everything is working.

      Thanks for steering me in the right direction.

       

      K.