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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
M_SBS_6
Helper V
Helper V

Aggregate Running Total Monthly

Hi,

I have the following measure on my line chart which shows a cumulative view by date. When my x-axis has day/month/quarter/year the lines increases cumulatively. 

 

My legend is Prod[type]

 

Run_total=

Calculate(sum(prod[amt],

Filter( Allselected (prod),

Prod[date] <= max prod[date])))

 

However, when I amend the date hierarchy to just show month, I just get the values, not cumulative. 

 

Any idea what I need to do to get the total by month so the x-axis shows Jan to December with the cumulative value? 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Use the date from your calendar dimension table, not the prod fact table.

View solution in original post

v-yohua-msft
Community Support
Community Support

Hi, @M_SBS_6 

To achieve a cumulative total by month, you can modify the measure to ensure that it matches the monthly context.
You might want to try the DAX expression below:

Run_total_Monthly = 
CALCULATE(
    SUM(prod[amt]),
    FILTER(
        ALLSELECTED(prod[date].[Month]),
        prod[date] <= MAX(prod[date])
    )
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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-yohua-msft
Community Support
Community Support

Hi, @M_SBS_6 

To achieve a cumulative total by month, you can modify the measure to ensure that it matches the monthly context.
You might want to try the DAX expression below:

Run_total_Monthly = 
CALCULATE(
    SUM(prod[amt]),
    FILTER(
        ALLSELECTED(prod[date].[Month]),
        prod[date] <= MAX(prod[date])
    )
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

Use the date from your calendar dimension table, not the prod fact table.

gmsamborn
Super User
Super User

Hi @M_SBS_6 

 

I would like to help but I'm a little unclear about your requirements.

 

Can you provide the following?

 

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

 

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

3) Please explain how you would expect to get from step 1 to 2.

 

4) If possible, please show your past attempts at a solution.

 

 

A .pbix file with sample data would be best.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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