cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Reference a previous month using variables in DAX using slicers

Capture.JPG

 

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.

17 REPLIES 17
Super User I
Super User I

Re: Reference a previous month using variables in DAX using slicers

have you looked at the previousmonth and sameperiodlastyear etc functions in dax?

 

https://msdn.microsoft.com/en-us/library/ee634972.aspx

https://msdn.microsoft.com/en-us/library/ee634758.aspx

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Reference a previous month using variables in DAX using slicers

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.

Super User I
Super User I

Re: Reference a previous month using variables in DAX using slicers

@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 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Reference a previous month using variables in DAX using slicers

@vanessafvg

 

There is no week unfortunalty, only day/month/quarter/year, if we do -7

 

Something like 

 

 

internetTargets[weeknum]= WEEKNUM((DATEADD ( 'Calendar$'[Date], -7, DAY ))))

 

It does not allow you to use dateadd in a filter expression

 

FILTER ('Table', 'Table'[Date] ) = DATEADD ( 'Table'[Date], -7, DAY )

 

Super User I
Super User I

Re: Reference a previous month using variables in DAX using slicers

FILTER ('Table', 'Table'[Date] = DATEADD ( 'Table'[Date], -7, DAY ))

 you have a comma /bracket in the wrong place

 

this is how i would do it

CALCULATE(sum(internettarget), filter('Date', 'Date'[Date] = dateadd('Date'[Date], - 7, DAY)))


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Reference a previous month using variables in DAX using slicers

Hi Vanessa,

 

I am trying to get the full previous week rather than just one day

 

Thanks for helping though...much appreciated.

 

Chris

Super User I
Super User I

Re: Reference a previous month using variables in DAX using slicers

@Anonymous ag ok make sense, probably need to use week numbers then, perhaps using the function earlier could help? if you had a week number in your date table?

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Reference a previous month using variables in DAX using slicers

Yep got a week number in the date table but it doesnt seem to like using the weeknum function within an evaluation

Super User I
Super User I

Re: Reference a previous month using variables in DAX using slicers

@Anonymous show me your code?

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors