Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

This week date measure

Hi ,

can we create a measure to return all the dates that belongs to this week ? and last year same week ? 

 

this week dates =
var todaydate = TODAY()
var startdate= CALCULATE(SUM(DimTimeCalendar[WeekPeriodStarting]),DimTimeCalendar[Date_Key]=todaydate)
var enddate= CALCULATE(SUM(DimTimeCalendar[WeekPeriodEnding]),DimTimeCalendar[Date_Key]=todaydate)
RETURN
DATESBETWEEN ( Dimtimecalendar[date_key], StartDate, EndDate )
 
Like this i am using right now and storing it in a calculated table . Is this the right way ? or we can use a better way to do this .
 
let me tell you my original problem .. so i need in a tabular format this week sales and last week sales . in a same table 

kind of like this .. lw means last week . and i am giving the dax also 

GP$$ = SUM(FactSales[GpFexchangeRate])
 
GP Lw =
CALCULATE(
FactSales[GP$$],all(FactSales),
FactSales[Sale Date]=SELECTEDVALUE(FactSales[Sale Date])-7)
 
 
GP this week =
var datetoday=TODAY()
var thisweek=calculate(max(DimTimeCalendar[Fiscalweeknumber]),filter(all(DimTimeCalendar),DimTimeCalendar[Date_Key]=datetoday))
var thisyear=calculate(MAX(DimTimeCalendar[FiscalYear]),filter(all(DimTimeCalendar),DimTimeCalendar[Date_Key]=datetoday))
return
CALCULATE(
sum( FactSales[GpFexchangeRate]),
DimTimeCalendar[Fiscalweeknumber]=thisweek,DimTimeCalendar[Year]=thisyear
)
 
 
Is this the right way ? i am new to power bi . please help me to find a better solution 
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you want the matrix to show the values for this week and the previous week.

    Here's my solution.

    1.Create a calendar table.

    Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

    2.Create a measure, put it into visual level filters and set show items when the value is 1.

    Measure = var _thisweek=WEEKNUM(TODAY(),2)
var _week=WEEKNUM(MAX('Calendar'[Date]),2)
return IF(_thisweek=_week||_thisweek-1=_week,1)

     

    As shown above, the view shows the data for this week and the previous week.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous . Last year same weekday or same week

    measure

    week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

     

    OR new columns in date table 


    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measures
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

    or

     

    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -53)))

     


    Last year same week  = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    I think you want the matrix to show the values for this week and the previous week.

    Here's my solution.

    1.Create a calendar table.

    Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

    2.Create a measure, put it into visual level filters and set show items when the value is 1.

    Measure = var _thisweek=WEEKNUM(TODAY(),2)
var _week=WEEKNUM(MAX('Calendar'[Date]),2)
return IF(_thisweek=_week||_thisweek-1=_week,1)

     

    As shown above, the view shows the data for this week and the previous week.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • PabloGiraldo's avatar
      PabloGiraldo
      Helper IV
      3 years ago

      Hi Stephen, i am trying to create a filter that once i click on it it will show me "This Week" and "This Month."

       

      This example got me pretty close but wondering if you could me.

       

      Thank you,

      Pablo

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        3 years ago

        Hi PabloGiraldo 
        Could you please clarify what these periods of time mean?
        Does "today" refer to the day the user looks at the report?
        Does the current week start on "today" and last -7 days?
        Does the current month start on "today" and last -30 days?