Forum Discussion

svishwanathan's avatar
svishwanathan
Icon for Helper III rankHelper III
9 years ago

Measures for previous week

Good Afternoon

 

I have a very elaborate calendar table that gives me both the weeknumber in a given year...eg.  uptil 54 weeks in a year as well as cumulative week number like 180, 1802 because I have dates all the way upto 2018. 


Now comes the hard part where I need to use these in my calculations.

 

I am running a lot of measures in my report which give me aggregate values over the users

 

Now I want to be able to display values to date side by side with values for previous week

 

I dont know how to leverage the week number and cumulative week number in my calendar table to do this

 

For instance there is a measure like Total Sales, how do I write a new measure that gives me total sales for previous week

 

I came across a few posts already on similar lines but unfortunately I cannot seem to follow any of them

 

I am trying to avoid adding any more calcualted columns in my calendar table as my team is always asker for learner data sets.

 

Tried this but obviously doesnt work

 

CALCULATE([Productivity],ALLEXCEPT('Calendar','Calendar'[Week Number]=WEEKNUM(TODAY()-1)))

 

All help and any help is greatly appreciated

 

Need Help

1 Reply

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    I think the easiest way to accomplish this is to enrich your calendar with a prior week column, your dax measures become must easier to read and debug. simple measure would look like

    calculate(sum(something), filter(all('dates'), 'dates'[priorweekvalue] = max('dates'[priorweekvalue]))

     

    hope that helps a little.

     

    Richard