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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Measure required for "Quarterly Cumulative Running Total vs. Same Period Last Year"

Dear Experts,

I have a very simple table of transactional data of sales of two years and want to create a measure for :

"Quarterly Cumulative Running Total vs. Same Period Last Year"

Please note that I need cumulative running total not just total. You can see my sample data here with required table. Please suggest the DAX measure.

https://www.dropbox.com/s/blbczrr3c7d907q/Sales%20data.xlsx?dl=0

Regards,

Imran

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

You may create two calculated columns and a measure as below.

``````Calculated column:
Qtr = QUARTER('Table'[Date])
Year = YEAR('Table'[Date])

Measure:
Result =
var _qtr = SELECTEDVALUE('Table'[Qtr])
var _year = SELECTEDVALUE('Table'[Year])
var _amount =
CALCULATE(
SUM('Table'[Sales Amount]),
FILTER(
ALLSELECTED('Table'),
'Table'[Qtr]=_qtr&&
'Table'[Year]=_year
)
)

return
IF(
NOT(ISBLANK(_amount)),
CALCULATE(
SUM('Table'[Sales Amount]),
FILTER(
ALLSELECTED('Table'),
'Table'[Qtr]<= _qtr&&
'Table'[Year]=_year
)
)
)``````

Result:

Best Regards

Allan

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

12 REPLIES 12
Community Support

Hi, @Anonymous

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

Anonymous
Not applicable

Thanks for youe message. Actual I didnt get the solution yet so I am keeping it open. Please see my last reply on this topic and help me to do the required.

Regards

Community Support

Hi, @Anonymous

Based on your description, I created data to reproduce your scenario.

Table:

Calendar (a calculated table):

``Calendar = CALENDARAUTO()``

There is a  one-to-one relationship between two tables.

Here are measures for accumulating values quarterly for each year

``````CurrentYearResult =
CALCULATE(
SUM('Table'[Value]),
DATESYTD('Calendar'[Date])
)

LastYearResult =
CALCULATE(
SUM('Table'[Value]),
DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]))
)``````

Here are measures for accumulating values for months in each quauter and each year.

``````CurrentQuarterResult =
CALCULATE(
SUM('Table'[Value]),
DATESQTD('Calendar'[Date])
)

LastQuarterResult =
CALCULATE(
SUM('Table'[Value]),
DATESQTD(SAMEPERIODLASTYEAR('Calendar'[Date]))
)``````

Results:

Best Regards

Allan

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

Anonymous
Not applicable

Please also send me the pbix file. I will apply on my data and will definitly come back.

Community Support

Hi, @Anonymous

Here is my pbix file for test.

Best Regards

Allan

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

Anonymous
Not applicable

This is working fine in case f two full years. In case of 2020 where we only one complete quarter, I am having the following sample visual.

This should be only cumulative of 2020, Q1 & Q2 (Apr data) but for Q3 & Q4 of 2020, there should be no bars.

Please suggest what to do.

Community Support

Hi, @Anonymous

Based on your sample data, I created data to reproduce your scenario.

Table:

Calendar:

``Calendar = CALENDARAUTO()``

There is a one-to-one relationship between two tables based on Date field.

You may create a measure as below.

``````Running Total =
CALCULATE(
SUM('Table'[Sales Amount]),
DATESYTD('Calendar'[Date])
)``````

Then you need to creata a matrix visual and here is the expected result.

Best Regards

Allan

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

Anonymous
Not applicable

Thanks for your effort and reply.

Please see my data again where i have created the desired scenario. the Required chart should look like the following. It means that if there is no data in any quarter, the chart should show no bar.

Here is the link to my data.

https://www.dropbox.com/s/blbczrr3c7d907q/Sales%20data.xlsx?dl=0

Regards,

Community Support

Hi, @Anonymous

You may create two calculated columns and a measure as below.

``````Calculated column:
Qtr = QUARTER('Table'[Date])
Year = YEAR('Table'[Date])

Measure:
Result =
var _qtr = SELECTEDVALUE('Table'[Qtr])
var _year = SELECTEDVALUE('Table'[Year])
var _amount =
CALCULATE(
SUM('Table'[Sales Amount]),
FILTER(
ALLSELECTED('Table'),
'Table'[Qtr]=_qtr&&
'Table'[Year]=_year
)
)

return
IF(
NOT(ISBLANK(_amount)),
CALCULATE(
SUM('Table'[Sales Amount]),
FILTER(
ALLSELECTED('Table'),
'Table'[Qtr]<= _qtr&&
'Table'[Year]=_year
)
)
)``````

Result:

Best Regards

Allan

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

Resolver IV

Hi @Anonymous

Can you try the following measure below:

``Total = CALCULATE(SUM(Sheet1[Value]), FILTER(ALLSELECTED(Sheet1), Sheet1[Date] <= MAX(Sheet1[Date])))``

Below is a screenshot of my data table and the quarterly cumulative running totals for 2019 & 2020.

A link to the tutorial where I found this: https://www.youtube.com/watch?v=RVcJ3O_O2Jg

Hope this helps!

Anonymous
Not applicable

Thanks for your reply. Yes, I already have used this measure but it does not serve the purpose. I want to create a bar chart showing Quarters of both the years with their running total.

In the measue you suggested I cant put it in one table. The objective here is to have Quarterly cumulative total of 2019 vs. quarterly cumulative totoal of same period last year in one single table so that it can be converted to bar chart.

Regaards,

Super User

So, I would recommend a Year and Quarter column using YEAR and QUARTER. Then you could create a cumulative measure like:

Measure =

VAR __Year = MAX('Table'[Year])

VAR __Quarter = MAX('Table'[Quarter])

RETURN

SUMX(FILTER('Table','Table'[Year] = __Year && 'Table'[Quarter] <= __Quarter),'Table'[Sales Amount])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors