Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help with DAX Measure Please !

Hi Community,   I need help in creating DAX Measure that calculates average of last 7 days, my output should start from 7th Row and the rows before 7th should not show any value. Refer screenshot b...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Like many people described, I also want to suggest using a proper calendar table.

    But, check the below picture and the attached pbix file, which I did not create a calendar table.

    All measures are in the attached pbix file.

     

     

    avg of last 7 days : =
    VAR currentdate =
    MAX ( Data[Date] )
    VAR countdates =
    CALCULATE (
    COUNTROWS ( VALUES ( Data[Date] ) ),
    FILTER ( ALL ( Data[Date] ), Data[Date] <= currentdate )
    )
    VAR sevendaysperiod =
    DATESBETWEEN ( Data[Date], currentdate - 6, currentdate )
    VAR result =
    AVERAGEX ( sevendaysperiod, [Sales measure :] )
    RETURN
    IF ( countdates < 7, BLANK (), result )

     

     

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

If you have a calendar table and it's properly related to your fact table, then a measure like this should work:

 

_avgSales7Day =
CALCULATE(
  AVERAGE(yourTable[Sales]),
  DATESINPERIOD(
    calendar[Date],
    LASTDATE(calendar[Date]),
    -7,
    DAY
  )
)

 

 

If you don't have a calendar table set up, then I highly recommend you get one (examples are easily found online).

Many DAX functions rely on a proper calendar table to work, thus making your life significantly easier.

 

Pete