Sum values by certain months

[Anonymous]

Hey guys, I'm looking to take a return a certain value to my line graph.

I have two tables, which are currently related.  Table A and Table B.

This is my current equation:

DIVIDE(CALCULATE(SUM(TableA['TotalSpent]),TableA['Months] in {"July 2018","August 2018","September 2018}),

SUM(TableB['TotalAvailable'],0))

On my Line graph, I'm going to have Month and Year as the x-axis and the percentage of that dax measure above shown as the y -axis.  I want to use that function above, but in my filter, take the current month and the next 2 months and divide by the Total Available for every Month and Year in the X axis.  Is there a way to change the filter so I dont have type in the specific date range I want to select?

For example:  In the X-axis for date, if the point is July 2018, it would be the Sum of Total Spent in July 2018, August 2018, and September 2018 (table A) / Sum Total Available for July 2018 (table B)

The next month (August 2018) would then be the Sum of Total Spent in August 2018, September 2018, October 2018 (table A) / Sum Total Available  for August 2018(table B)

Thanks!

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a calendar table and get the rank of the YearMonth column.Then create measures as below.Attached sample file for your reference.

Sum of Total Spent =
CALCULATE (
    SUM ( TableA[Spent] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Rank] >= MAX ( 'Calendar'[Rank] )
            && 'Calendar'[Rank]
                <= MAX ( 'Calendar'[Rank] ) + 2
    )
)

Regards,

[Anonymous]

@v-cherch-msft 

Is it possible to Inner join by ID's when rolling?  Meaning that I want to Roll 3 months where Table B ID's are in Table A ID's, and by month,year.

[v-cherch-msft]

Hi @Anonymous 

Yes,it's possible.You may link tableA and tableB with ID.For further,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,