Converting Year to Date Value to Quarterly

[culytics]

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

[Ashish_Mathur]

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.

[v-yiruan-msft · Accepted solution]

Hi @culytics ,

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.

[tamasstahl]

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!

[amitchandak]

@culytics , 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.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[vanessafvg]

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.