Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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
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
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
Proud to be a Super User!
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
Proud to be a Super User!
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)))
Proud to be a Super User!
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?
Proud to be a Super User!
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?
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
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
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |