DAX formula for counting number of days in the quarrter

[Anonymous]

I'm new to Power BI. What's the DAX formula to calculate the number of days in the current quarter assuming 10/01/18 is the start of the quarter and today? It would like the formula to always calculate the beginning of the quarter and end of the quarter is today (QTD). I've attched a simple table below for reference.

Date	Water Production	Day Count	QTD Avg
10/1/2018	100	1
10/2/2018	20	1
10/3/2018	300	1
10/4/2018	400	1
10/5/2018	101	1
10/6/2018	303	1
10/7/2018	306	1
10/8/2018	401	1
10/9/2018	102	1
10/10/2018	309	1
10/11/2018	312	1
10/12/2018	402	1
10/13/2018	103	1
10/14/2018	315	1
10/15/2018	318	1
10/16/2018	403	1
10/17/2018	104	1
10/18/2018	321	1
10/19/2018	324	1
10/20/2018	404	1
10/21/2018	105	1
10/22/2018	327	1
10/23/2018	330	1
10/24/2018	405	1
10/25/2018	106	1
10/26/2018	333	1
10/27/2018	336	1
10/28/2018	406	1
10/29/2018	107	1
10/30/2018	339	1
10/31/2018	342	1
11/1/2018	407	1
11/2/2018	108	1
11/3/2018	345	1
11/4/2018	348	1
11/5/2018	408	1
11/6/2018	109	1
11/7/2018	351	1
11/8/2018	354	1
11/9/2018	409	1
Total	11,323	40	283.08
	(a)	(b)	=(a)/(b)

[Anonymous]

@Anonymous,

Please create the following columns in your table and if the DAX below doesn't help, please post expected result based on above sample data here.

start of quarter = STARTOFQUARTER(Table[Date])  

daynumber = DATEDIFF(Table[start of quarter],Table[Date],DAY)

Column = DATEDIFF(Table[start of quarter],TODAY(),DAY)

Regards,
Lydia

[Anonymous]

Lydia - thanks for the feedback.  however, I couldn't get the "daynumber" to work.  see error.

 

[Anonymous]

@Anonymous,

Do you create calculated column? If so, please change your formula to the following:

daynumber = DATEDIFF(Table[start of quarter],RELATED('Calendar'[Date]),DAY)

If you are creating measure, please use DAX below.

daynumber = DATEDIFF([start of quarter],max('Calendar'[Date]),DAY)

Regards,

 Lydia

[Greg_Deckler]

OK, I have a couple of formulas for you, one is a column and the other is a measure.

Column:

QTD Avg = 
VAR __currentYear = YEAR([Date])
VAR __currentQuarter = [Quarter]
VAR __table = FILTER(ALL(Table5),YEAR([Date])=__currentYear && [Quarter]=__currentQuarter && [Date]<=EARLIER([Date]))
RETURN
//DIVIDE(SUMX(__table,[Water Production]),COUNTX(__table,[Date]),0)
AVERAGEX(__table,[Water Production])

Measure:

QTD Avg Measure = 
VAR __currentYear = YEAR(MAX([Date]))
VAR __currentQuarter = MAX([Quarter])
VAR __currentDate = MAX([Date])
VAR __table = FILTER(ALL(Table5),YEAR([Date])=__currentYear && [Quarter]=__currentQuarter && [Date]<=__currentDate)
RETURN
//DIVIDE(SUMX(__table,[Water Production]),COUNTX(__table,[Date]),0)
AVERAGEX(__table,[Water Production])

Now, for the measure in a full scenario you will likely run into issues the the total so refer to this Quick Measure to resolve that

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

And, not sure about the whole TODAY thing but you could just replace __currentDate with = TODAY().