Forum Discussion

rush's avatar
rush
Icon for Helper V rankHelper V
9 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 ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 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
    Icon for Helper V rankHelper V
    9 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
      Icon for Microsoft Employee rankMicrosoft Employee
      9 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

      • rush's avatar
        rush
        Icon for Helper V rankHelper V
        9 years ago

        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.