PowerBi Rolling Sum

[Anonymous]

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 · Accepted solution]

@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]

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]

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.