Forum Discussion

rush's avatar
rush
Helper V
8 years ago

Aggregate data based off previous data in a measure

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

 

Need Help

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi rush,

     

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

    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's avatar
      rush
      Helper V
      8 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        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. :smileyhappy:

         

        Regards