Need Help with DAX Measure Please !

[Anonymous]

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 below.

Looking forward to your kind response

Thanks in advance!

Regards

Manoj

[Jihwan_Kim · Accepted solution]

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 )

[Anonymous]

Hi Sir,

Above Dax provided by you really helps, but when I am putting some filtering criteria like 07th of every month its not showing the average nos., seems like its working as sum of sales nos.

Including screenshots for your reference:
Before filtering Criteria:

After Filtering criteria:

Value of L7D is coming from the Dax provided by you which is correct before filtering criteria but after putting the filtering criteria of 07th day of every month L7D values are same as Daily Sales_HL.
Could you please help me in fixing this.
Regards

Manoj

[smpa01]

@Anonymous  if you have a table like this

| Date                        | Sales |
|-----------------------------|-------|
| Friday, January 1, 2021     | 2822  |
| Saturday, January 2, 2021   | 2340  |
| Sunday, January 3, 2021     | 1272  |
| Monday, January 4, 2021     | 1096  |
| Tuesday, January 5, 2021    | 2431  |
| Wednesday, January 6, 2021  | 1267  |
| Thursday, January 7, 2021   | 2705  |
| Friday, January 8, 2021     | 2452  |
| Saturday, January 9, 2021   | 2369  |
| Sunday, January 10, 2021    | 1770  |
| Monday, January 11, 2021    | 2938  |
| Tuesday, January 12, 2021   | 1741  |
| Wednesday, January 13, 2021 | 1043  |
| Thursday, January 14, 2021  | 1244  |
| Friday, January 15, 2021    | 2023  |
| Saturday, January 16, 2021  | 2500  |
| Sunday, January 17, 2021    | 2113  |
| Monday, January 18, 2021    | 2451  |
| Tuesday, January 19, 2021   | 1193  |
| Wednesday, January 20, 2021 | 2660  |
| Thursday, January 21, 2021  | 1161  |
| Friday, January 22, 2021    | 2838  |
| Saturday, January 23, 2021  | 1689  |
| Sunday, January 24, 2021    | 2258  |
| Monday, January 25, 2021    | 2484  |
| Tuesday, January 26, 2021   | 1540  |
| Wednesday, January 27, 2021 | 1565  |

You can write the following measure 

Measure1 = 
var _upper = MAX('Table'[Date])-6
var _lower = CALCULATE(MAX('Table'[Date]))
VAR _revSum = CALCULATE(AVERAGE('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]>=_upper&&'Table'[Date]<=_lower))
VAR _x = CALCULATE(MIN('Table'[Date]),ALL('Table'[Date]))
RETURN IF(_x<=_upper,_revSum)

to come to the following

[Anonymous]

Do I need to create a separate calendar table or the same date column will work.

[smpa01]

@Anonymous  no need to create seperate calendar table, works from the same table. If you disect the measure you would know.

[BA_Pete]

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