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 )

     

     

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
Icon for Community Champion rankCommunity 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.