Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Reference a previous month using variables in DAX using slicers

 

Hi I am trying to reference the last week using some dax code that Greg_Deckler provided.  I got the last week working but it only works after the firstr week in a given month.  How would I go about referencing the last week if it was last month or even between last month and this month.

 

Thanks for help.

Need Help

17 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi vanessafvg

       

      It is going of week number not months or last year.  I understand that when you use these it generates a table and I think it is due to table being constrained the month selected but for this purpose I need to sort of see if it is in the previous month and use that.

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        9 years ago

        Anonymous you can probably use use datedd with a filter

         

        for example

         

        CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    FILTER ('Table', 'Table'[Date] ) = DATEADD ( 'Table'[Date], -7, DAY )

        CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    FILTER ('Table', 'Table'[Date] ) = DATEADD ( 'Table'[Date], -1, WEEK)

         

        haven't test this this though 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago
    Anonymous wrote:

     

    Hi I am trying to reference the last week using some dax code that Greg_Deckler provided.  I got the last week working but it only works after the firstr week in a given month.  How would I go about referencing the last week if it was last month or even between last month and this month.

     

    Thanks for help.

     

    Hi Anonymous,

     

    Actually, I'm not sure what's your desired results. Take the sample data which provided by @smoupre in your previous thread, can you share us what's your desired results? I have attached the .pbix file which I created based on @smoupre in your previous thread, please download it and tell us your requirement clearly.

     

    Best Regards,
    Qiuyun Yu 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-qiuyu-msft

       

      I haved tested the previous week and it works fine with you model.

       

      Last Week = VAR myMax = MAX('Calendar'[Weeknum])-1 RETURN CALCULATE(SUM([Value]),ALLEXCEPT('Values','Values'[Weeknum]),'Values'[Weeknum]=myMax)

       

      For some reason it pulls through blank on our data.  I think it is because it is using different tables in my slicers?

       

      My code is

       

      Last Week = VAR myMax = MAX('Calendar$'[Weeknum])-1 RETURN CALCULATE(SUM(GA[Pageviews]),ALLEXCEPT(GA,GA[weeknum],GA[weeknum]=myMax)

      But my slicers are referencing

       

      calendar[Year]

      calendar[Month]

      JoinTable[Website]

      Calendar[Date]

       

      C