Forum Discussion

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi cham  , 

     

    First you need to have a table with a calendar. 

     

    table = Calendar (dateStart;dateEnd)

    After creat new column

    WeekNum = WEEKNUM('calendar'[Date];2) 
    where 2 -start week on Monday

    Create relationsship you table "Fact" with table "Calendar" through date. 

    New measure: Fact(WeekNow) , 

     

    Fact_weekNow = calculate(sum(fact);
filter(Calendar;calendar[weekNum] = WEEKNUM(max('Fact'[Date]);2))
    Fact_weekPr = calculate(sum(fact);
filter(Calendar;calendar[weekNum] = WEEKNUM(max('Fact'[Date]);2)-1)
    WeekNowVSWeekPr= Fact_weekNow /Fact_weekPr -1

    Filter for weeks should be used from the calendar table, then you can see empty weeks if you need look fact at period

     

     

     

    • cham's avatar
      cham
      Icon for Post Patron rankPost Patron
      7 years ago

      Hi Anonymous 

       

      I want to know why you use 2 for followings,

      WeekNum = WEEKNUM('calendar'[Date];2) 
      where 2 -start week on Monday

       

      Fact_weekNow = calculate(sum(fact);
filter(Calendar;calendar[weekNum] = WEEKNUM(max('Fact'[Date]);2))
      Fact_weekPr = calculate(sum(fact);
filter(Calendar;calendar[weekNum] = WEEKNUM(max('Fact'[Date]);2)-1)

       

      In my data set i have client details. I want to get the difference betweeen previous week client values with this week.

       

       - In your formula you used sum(fact) so in my formula do i need to use sum(clients) ?

      - Do i need to create a seperate calender to calculate the data?

       

      regards,

      cham