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

[Anonymous]

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

[v-alq-msft]

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]

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]

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]

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

[v-alq-msft]

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]

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.

[v-alq-msft]

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]

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,

[v-alq-msft · 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:

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]

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]

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]

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])