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

Looking forward to your kind response

Thanks in advance!

Regards

Manoj

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 )

     

     

6 Replies

  • 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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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 )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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