Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-qiuyu-msft
Community Support
Community Support


@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

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

 

 

 

 

 

 

vanessafvg
Super User
Super User

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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.

@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 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@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 )

 

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)))





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Hi Vanessa,

 

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

 

Thanks for helping though...much appreciated.

 

Chris

@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?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

@Anonymous show me your code?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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)

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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.

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.