Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum values by certain months

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!

 

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-cherch-msft
Employee
Employee

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.