Aggregate data based off previous data in a measure

[rush]

Hi

I am trying to get my formula to accumulate based on the previous month's data and carry that forward with my monthly targets value but cannot seem to get it to work:

Since I do not have any values for October yet, I want to estimate  the rest of the months figures by adding up my monthly targets value for that month, I used an IF statement which needs to bring back the accumulative value being 12000 + my targets value monthly figure (8000) = 20000, then take 20000 + 10000 = 30000 and so on.

My Data Model:

Tables Targets and Billing_Info are joined through the Dim_Date table on Date.

Targets and Billing_Info are joined through table Dim_Sales_Consultant on Sales_Consultant

 NB: My monthly target amount is in a form of a measure which I had to calculate it off my target values.

CALCULATE(SUM(Targets[Target]),KEEPFILTERS(VALUES(Dim_Date[Calendar_Date].[Date])))

Billing Info GP running total = IF(Billing_Info[Total GP] = 0,
CALCULATE (
 SUM (Billing_Info[GP]),
 FILTER ( ALL ( Dim_Date), Dim_Date[Calendar_Date] <= MAX(Dim_Date[Calendar_Date]))) + Targets[Target per Month],
	
CALCULATE (
 SUM ( Billing_Info[GP] ),
 FILTER ( ALL ( Dim_Date), Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] ))))

 
Sample Data:
Billing Info Table
Target Sample

[v-ljerr-msft]

Hi @rush,

Based on my test, the formula below should work in your scenario. 

Billing Info GP running total = 
IF (
    SUM ( Billing_Info[GP] ) = 0,
    CALCULATE (
        SUM ( Billing_Info[GP] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
        )
    )
        + CALCULATE (
            SUM ( Targets[Target per Month] ),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
            )
        ),
    CALCULATE (
        SUM ( Billing_Info[GP] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
        )
    )
)

Regards

[rush]

@v-ljerr-msft thank you very much for which that that would work except I have to apologize that I forgot to mention that my monthly target is a measure:

Target per Month = 
		CALCULATE(SUM(Targets[Target]),KEEPFILTERS(VALUES(Dim_Date[Calendar_Date].[Date])))

[v-ljerr-msft]

Hi @rush,

Could post your real table structures(including the relationships and measures you're using) with some sample/mock data? It's better that you can share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[rush]

@v-ljerr-msft 

Please see above where I have taken an image of the relationships between the working tables. I have added the links to the 2 tables that contain dummy data as well.

I have a Date_Dim which is a calculated table:

Dim_Date = CALENDAR (DATE(1997,1,1), DATE(2080,12,31))

Your help is much appreciated.

[v-ljerr-msft]

Hi @rush,

I have tried to download your shared sample data for testing. However, it seems that I don't have access to.

Anyway, could you try the formula(untested) below to see if it works in your scenario? 

Billing Info GP running total =
IF (
    SUM ( Billing_Info[GP] ) = 0,
    CALCULATE (
        SUM ( Billing_Info[GP] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
        )
    )
        + SUMX (
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
            ),
            [Target per Month]
        ),
    CALCULATE (
        SUM ( Billing_Info[GP] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Calendar_Date] <= MAX ( Dim_Date[Calendar_Date] )
        )
    )
)

Regards

[rush]

@v-ljerr-msft Any luck?

I can get the figures to take each month's target then add on to the last available amount being September:

What I require:

 

[rush]

@v-ljerr-msftThank you for your feedback but unfortunately, it is not adding correctly.

It is taking the targets total amount and not the [Target per month] for some reason.

I have edited the links for the sample data which should work now.