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 )

     

     

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.