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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Running Total returns wrong value

Hi all,

 

I'm trying to figure out the running total quick measures. I've added in the base as the sum of couriers and the field as the start date. 

 

As you can see below, the running totals aren't correct. Could you let me know what I've done wrong please? I'm trying to calculcate how many couriers there are per month in each quarter & year. 

 

Thanks! 

 

cchevonne_0-1634909550432.png

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Anonymous 

You need to create a calendar table with distinct dates, and connect that date column to your current created at date. Then you would use the new calendar date in the filter expression. For example, if you data is from 2020 to 2021, try

 

1. Create a calendar table =Calendar(date(2020,11), date(2021,12,31))
2. Connect the new date table with Created_At. 

3. create the measure:

Measure = calculate(sum(sheet1[value]), Filter(allselected(calendar table), isonorafter(calendar table[date], max(calendar table[date]), desc))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@Anonymous 

You need to create a calendar table with distinct dates, and connect that date column to your current created at date. Then you would use the new calendar date in the filter expression. For example, if you data is from 2020 to 2021, try

 

1. Create a calendar table =Calendar(date(2020,11), date(2021,12,31))
2. Connect the new date table with Created_At. 

3. create the measure:

Measure = calculate(sum(sheet1[value]), Filter(allselected(calendar table), isonorafter(calendar table[date], max(calendar table[date]), desc))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

YukiK
Impactful Individual
Impactful Individual

Are you using Is_created_at in the table? Or different columns? The same formula works like a charm on my end:

YukiK_0-1634913660854.png

 

Anonymous
Not applicable

They are columns and not from the table. I wasn't sure how to reference to the column. 

 

I'll try your formula, thanks

 

YukiK
Impactful Individual
Impactful Individual

You'd need to create a relationship between that table and your Sheet1 on Created_at column. And When you create a quick measure, don't place created_at column but instead, place date column that's on the related table.

 

Hope that makes sense

Anonymous
Not applicable

Could you send me a screenshot of your relationships please? 

YukiK
Impactful Individual
Impactful Individual

This is just test tables. Please adjust according to your model. You can drag and drop a column to another column in a different table

YukiK_0-1634915371624.png

 

Please gives a thumbs up if this is helpful!

Anonymous
Not applicable

I wasn't able to do a many to one. I don't quite understand relationships so I think I might have to go and read up on it then figure out how to reference the table in the running total. 

 

cchevonne_0-1634915669785.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors