DAX Query

[Anonymous]

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 🙂

[Cristian_Angyal]

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

[Anonymous]

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: