Capture closing number by week number

[irenelitw629]

Hello,

Anyone can teach me how to write a measure to capture closing number of each quarter?

My data has weekly closed number by quarter as per below. And I want to write a measure to capture quarter closing number.

In this example, 19-Q1 closing number is at week 13 for $47966 ; 19-Q3 at week 14 for $71328

Thank you for your advice.

Week	1	2	3	4	5	6	7	8	9	10	11	12	13	14
19-Q1	605	730	729	1414	3668	4309	4612	7607	8021	13659	14327	25473	47966
19-Q2	2998	3854	3854	14135	15097	15097	16762	19442	22661	22661	23705	24185	101414
19-Q3	6086	7134	7288	8744	9591	11571	13158	14474	23088	24695	26155	30768	72133	71328
19-Q4	0	343	70	70	1156	20100	24987	29104	32976	51703	51859	111615	119878	126729

[V-lianl-msft · Accepted solution]

Hi @irenelitw629 ,

You can perform the "unpivot" operation in "edit queries"

Make sure the "Attribute" column is the type of whole number.

Then you can use DAX like this:

Measure = CALCULATE(

            MAX(Sheet1[Value]),

            FILTER(Sheet1,Sheet1[Attribute]=MAX(Sheet1[Attribute])))

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

[amitchandak]

Are these week numbers based on Qtr and you need qtr trend with the last week in QTR