Forum Discussion

VladMatao's avatar
VladMatao
Frequent Visitor
6 years ago

getting previous week value

Could someone tell me what is wrong with my measure?
 
measure =
var previousweek=
if(
MOD(SELECTEDVALUE('table'[yearweek]),100)=1,
SELECTEDVALUE('table'[yearweek])-49,
SELECTEDVALUE('table'[yearweek)-1
)
var prev week value =
CALCULATE(
SUM('table'[value]),
FILTER('table','table'[yearweek]=previousweek)
)
return prev week value

 

What I am expecting:

prev week valueyearweekvaluecategorycat_type
3020200110a4g
1020200220a4g
4020195230a4g
...20195140a4g

 

I get a blank table (I have no filters or agregation on) and if I'm taking the measure out of the table they start to pop up.

 

 

 

 

 

 

 

 

4 Replies

  • You have to use all

    var prev week value =
    CALCULATE(
    SUM('table'[value]),
    FILTER(all('table'),'table'[yearweek]=previousweek)
    )
    return prev week value

     

    It is better to take week to a different table or preferably to date table. Create a rank.

    Week rank = Rank(Week,Week[yearweek],,asc,dense)

     

    var prev week value =

    Var previousweekRank= maxx(Week,Week[Rank])-1
    CALCULATE(
    SUM('table'[value]),
    FILTER(all('Week'),'Week'[Rank]=previousweekRank)
    )
    return prev week value

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @

    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support

    Hi  VladMatao ,

     

    You have to modify your measure as below:

     

    Measure =
    VAR previousweek =
        IF (
            MOD ( SELECTEDVALUE ( 'table'[yearweek] ), 100 ) = 1,
            SELECTEDVALUE ( 'Table'[yearweek] ) - 49,
            SELECTEDVALUE ( 'Table'[yearweek] ) - 1
        )
    VAR prevweekvalue =
        CALCULATE (
            SUM ( 'table'[value] ),
            FILTER ( ALL ( 'Table' ), 'table'[yearweek] = previousweek )
        )
    RETURN
        prevweekvalue

     

    Finally you will see:

     

     

    For the related .pbix file,pls click here.

     

     

     
    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

     

  • VladMatao's avatar
    VladMatao
    Frequent Visitor

    v-kelly-msft amitchandak 

    I think this one doesn't work because the "cat_type" and "category" will change, and if I want to show it for certain category types for example will give me the wrong value. I managed to do it with a column:

    lastweekvalue = CALCULATE(SUM('table'[value]),FILTER('table',EARLIER('table'[category])='table'[category] && EARLIER('table'[KPI_type])='table'[KPI_type] && EARLIER('node'[cat_type])='table'[cat_type] && EARLIER('table'[year week])='table'[year week]+if(MOD('table'[year week],100)=52,49,1)))

     But I would still like to know if this is possible with a measure

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support

      Hi VladMatao ,

       

      Can you show me your .pbix file if there's no confidential information inside?

       

       
      Best Regards,
      Kelly