topic Re: DAX Query in Custom Visuals Development Discussion

DAX Query

Anonymous — Tue, 21 Jan 2020 11:55:39 GMT

Hi Community,

I am new to Power BI and hence I'd need help with DAX Formulas.

I have two columns 1) Week (the date values are in 7 days interval) 2) Quantity

I would need the Minimum, Maximum and Average of Quantity based on the week column(Precisely past 6 weeks from the current week) - I will be using these aggregates in the Line values in a chart over the weeks. My question is:

1) Do I create a Measure or a new column

2) Syntax of the Dax query would be helpful.

Thanks 🙂

Re: DAX Query

Cristian_Angyal — Mon, 09 Mar 2020 15:26:26 GMT

Do you have a sample dataset and the required end-result? It would be much easier

Re: DAX Query

Anonymous — Tue, 17 Mar 2020 10:15:11 GMT

Hopefully this should set you on your way:

SixWeeksHence = lastdate(DATESINPERIOD(quantityTable[Week],calculate(firstdate(quantityTable[sixWeeksPrevious])),7*7,day)) FromDate = FIRSTDATE(DATESINPERIOD(quantityTable[Week],calculate(firstdate(quantityTable[sixWeeksPrevious])),7*7,day)) MinQuant = MINx( filter(quantityTable, quantityTable[Week] < earlier(quantityTable[SixWeeksHence]) && quantityTable[Week] >= earlier(quantityTable[FromDate])) ,quantityTable[Quantity]) AvgQuant = AVERAGEX( filter(quantityTable, quantityTable[Week] < earlier(quantityTable[SixWeeksHence]) && quantityTable[Week] >= earlier(quantityTable[FromDate])) ,quantityTable[Quantity]) MaxQuant = MAXX( filter(quantityTable, quantityTable[Week] < earlier(quantityTable[SixWeeksHence]) && quantityTable[Week] >= earlier(quantityTable[FromDate])) ,quantityTable[Quantity])

Data in PBI:

Data in Excel confirming results: