cancel
Showing results 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

## Converting Year to Date Value to Quarterly

I have a table in this format. With the values in black given and need to calculate values in purple. What is the best way to do it in PowerBI? The table has data for 4 years starting in 2017.

 Year/Quarter Name YTD Value Calculated Value Q4-2018 N1 342 52 Q1-2018 N1 100 100 Q2-2018 N1 205 105 Q3-2018 N1 290 85

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

You can follow the below steps to get it:

1. Create a calculated column to get the index base on the field [Year/Quarter]

``Index = RANKX('Sales',CONCATENATE(RIGHT('Sales'[Year/Quarter],4),LEFT('Sales'[Year/Quarter],2)),,ASC,Dense)​​``

2. Create a measure to get the difference with current value and the value of previous quarter

``````Calculated Value =
var _curindex=MAX('Sales'[Index])
var _preindex=CALCULATE(MAX('Sales'[Index]),FILTER(ALL('Sales'),'Sales'[Index]<_curindex))
var _curvalue=SUM('Sales'[YTD Value])
var _prevalue=CALCULATE(SUM('Sales'[YTD Value]),FILTER(ALL('Sales'),'Sales'[Index]=_preindex))
return _curvalue -_prevalue``````

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

Hi,

Write these 2 calculated column formulas

``Year = =1*(RIGHT(Data[Year/Quarter],4))``
``=Data[YTD Value]-LOOKUPVALUE(Data[YTD Value],Data[Year/Quarter],CALCULATE(MAX(Data[Year/Quarter]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Year]=EARLIER(Data[Year])&&Data[Year/Quarter]<EARLIER(Data[Year/Quarter]))),Data[Name],Data[Name],Data[Year],Data[Year])``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @Anonymous ,

You can follow the below steps to get it:

1. Create a calculated column to get the index base on the field [Year/Quarter]

``Index = RANKX('Sales',CONCATENATE(RIGHT('Sales'[Year/Quarter],4),LEFT('Sales'[Year/Quarter],2)),,ASC,Dense)​​``

2. Create a measure to get the difference with current value and the value of previous quarter

``````Calculated Value =
var _curindex=MAX('Sales'[Index])
var _preindex=CALCULATE(MAX('Sales'[Index]),FILTER(ALL('Sales'),'Sales'[Index]<_curindex))
var _curvalue=SUM('Sales'[YTD Value])
var _prevalue=CALCULATE(SUM('Sales'[YTD Value]),FILTER(ALL('Sales'),'Sales'[Index]=_preindex))
return _curvalue -_prevalue``````

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

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

Hi, could you help me what I should do if I have multiple names like N1, N2, etc. I think I am making a mistake when indexing.

Another question is what should I do if I have 0 for Q2 and larger values for Q1 and Q3. In this case the above code gives me a negative value.

Thanks!

Super User

@Anonymous , if new columns, have these three

qtr year = right([Year/Quarter],4) & left([Year/Quarter],2)
qtr year rank = rankx(all(Table), [qtr year], , asc, sense)

diff with last qtr = [Value] - sumx(filter(Table, [qtr year rank] =earlier([qtr year rank])-1),[Value])

If you need rank, create a new table for qtr year Say Date and have this rank column there and try measure like

column

qtr year = right([Year/Quarter],4) & left([Year/Quarter],2)

Qtr Rank = RANKX(all('Date'),'Date'[qtr year],,ASC,Dense)

measure
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Super User
what is the calculated value in purple? you need to give more of an an explanation, it doesn't make sense currently with what you have provided.

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.