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

Reply
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

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:

i1.png

 

Best Regards

Allan

 

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

12 REPLIES 12
v-alq-msft
Community Support
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

v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Table:

e1.png

 

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:

e2.png

e3.png

 

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.

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.

 

QtrvsSPLY.jpg

Hi, @Anonymous 

 

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

Table:

c1.png

 

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.

c2.png

 

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.

Required QoverQ.png

 Here is the link to my data.

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

 

Regards,

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:

i1.png

 

Best Regards

Allan

 

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

bheepatel
Resolver IV
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.

 

Capture.JPG

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,

Greg_Deckler
Super User
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])


Follow on LinkedIn
@ 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors