Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX help for a Restricted Measure

Dear Experts

 

I have the YearWeek, Sales and some more fields coming from 4 different tables. I want to display in a new column the last 6 weeks sales figure based on System Week.

 

 

This is basically restricting the sales value for the latest 6 weeks.

 

I want to achieve this using a measure. Please can someone help me with the DAX code to help me achieve this.

 

Kind regards
Mahad

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Please try

    Last 6 Weeks Sales =
VAR CurrentYearWeek =
    SELECTEDVALUE ( TableName[YearWeek] )
VAR TodayYearWeek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () )
VAR StartYearWeek = TodayYearWeek - 6
RETURN
    IF (
        CurrentYearWeek <= TodayYearWeek
            && CurrentYearWeek > StartYearWeek,
        [Sales Amount]
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    Please try

    Last 6 Weeks Sales =
VAR CurrentYearWeek =
    SELECTEDVALUE ( TableName[YearWeek] )
VAR TodayYearWeek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () )
VAR StartYearWeek = TodayYearWeek - 6
RETURN
    IF (
        CurrentYearWeek <= TodayYearWeek
            && CurrentYearWeek > StartYearWeek,
        [Sales Amount]
    )
  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi Anonymous , Do you have a sanitised version of your report you could share? You could achieve this with DATESINPERIOD and a workaround as DIP only uses, days, months or years in the formula.