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

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

Last Week Targets = VAR myMax = MAX('Calendar$'[Weeknum]) RETURN CALCULATE(SUM(internetTargets[Impressions]),ALLEXCEPT(internetTargets,internetTargets[weeknum],joinTable[Website],'Calendar$'[Year],'Calendar$'[Month Name]),internetTargets[weeknum]=myMax-1)
Super User I
Super User I

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

@Anonymous will look into later for you

 

not really sure what you trying to do in that calculation though can you give an explanation before i attempt to rewrite it


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

Sure,  basically i am trying to sum the previous weeks values.

 

That calculation gives this weeks value but when I select a dat near the beginning or end of the month the values dont work.  I think it is becasue the slicer sets the table to on the the months selected so the previous week does not work.

Super User I
Super User I

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

ok just that your code looks a bit stranger to me i am not sure what you doing

 

Last Week Targets = VAR myMax = MAX('Calendar$'[Weeknum]) RETURN CALCULATE(SUM(internetTargets[Impressions]),ALLEXCEPT(internetTargets,internetTargets[weeknum],joinTable[Website],'Calendar$'[Year],'Calendar$'[Month Name]),internetTargets[weeknum]=myMax-1)

 

firstly it looks like you using 2 tables, is the week number in the calendar table?

secondly your relationships between your date table and internettargets is there one?

 

essentially though you need to be doing a filter something to this effect

Last Week Targets = VAR myMax = MAX('Calendar$'[Weeknum])

 

RETURN CALCULATE(SUM(internetTargets[Impressions]), filter('Calendar$', 'Calendar$'[Year]' = Year(now()) &

mymax='calendar'[weeknum]-1)

am sure this isn't right but need to know more about how your data is structured

 

 


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 

 

three tables 

 

one contains date

two contains internet page impressions targets

three contains ga page impressions

 

There is a week num column in each table and 2/3 joins the calendar table via the date column

 

 

Anonymous
Not applicable

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

Tried what you suggest but it throws up the error

 


MdxScript(Model) (9, 73) Calculation error in measure 'GA'[Last Week Targets]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

All values are whole numbers.....

 

After some more testing it seems it does not like the YEAR function

 

Moderator v-qiuyu-msft
Moderator

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


@Anonymous wrote:

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.


 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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

 

 

 

 

 

 

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors