cancel
Showing results for
Did you mean:
Frequent Visitor

## Custom QTR and sum by running year

Hi team,

I have a column that is connected to a SSAS cube and has multiple dimensions and measures and we have a custom quarter already built in, but i need to have a running QTR total by Fiscal Yr. and also calculate the % trend. any help will be much appreciated, havnt worked much on PBi, a step by step guide will really help to learn and share.

3 REPLIES 3
Super Contributor

## Re: Custom QTR and sum by running year

Hi @navrichard,

You could create a new measure:

`Measure = CALCULATE(SUM(YourTable[\$Cumm.]),FILTER(ALL(YourTable),'YourTable'[\$Cumm.]<=MAX('YourTable'[\$Cumm.])))`
`% trend = CALCULATE(SUM(YourTable[%Trend]),FILTER(ALL(YourTable),'YourTable'[%Trend]<=MAX('YourTable'[%Trend])))`

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Custom QTR and sum by running year

Hi Daniel

Thank you for your reply, but unfortunately the numbers are incorrect as seen in the first image

What i Ma trying to do is as below

Super Contributor

## Re: Custom QTR and sum by running year

Hi @navrichard,

Based on my test, you could refer to below steps:

Create three calculated columns:

`Mid2 = MID('Table1'[FYQTR],3,2)`
`Cumm = CALCULATE(SUM(Table1[S(Mn)]),FILTER('Table1','Table1'[FYQTR]<=EARLIER(Table1[FYQTR])&&'Table1'[Mid2]=EARLIER(Table1[Mid2])))`
```Trend = var a=SUBSTITUTE('Table1'[FYQTR],MID('Table1'[FYQTR],3,2),[Mid2]-1)
var b=CALCULATE(SUM('Table1'[Cumm]),FILTER('Table1','Table1'[FYQTR]=a))
var c= IF(b=BLANK(),BLANK(),IF(Table1[Cumm]=0,0,([Cumm]-b)/b))+0
return IF([Cumm]=0,BLANK(),c)```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 493 members 4,287 guests
Recent signins: