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

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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? :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] )
        )
    )
        + 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's avatar
        rush
        Icon for Helper V rankHelper V
        9 years ago

        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.