cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joebend New Member
New Member

Last N Weeks displayed on a chart different to result in Card

Hello,

 

Racking my brain to try and understand what (probably very simple) I am missing here.

 

I have some data that I am creating a measure for to show the last N number of weeks Sales based upon a parameter. I am using the following DAX and this works when displayed in a card.

 

However, when I add the measure to a line graph/ bar chart and add the date in the x axis it shows me all dates, not just the last N number that I am looking for.

 

The chart I would be expecting to see would only contain the dates of the last 4 weeks on the x axis, not all as show below.

 

LNW Sales = CALCULATE([Switch Sales], DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]),-(LastN[LastN Value]*7),DAY))

Sample.PNG

 
 
1 REPLY 1
Super User I
Super User I

Re: Last N Weeks displayed on a chart different to result in Card

I'll have a go at this since no-one else has chipped in.

 

My first recommendation is to use a date table (with a relation to the fact table) whenever time intelligence functions are called.  A lot of problems are solved by doing this.  So that first bit after DATESINPERIOD should be a date column from a date table.

 

The next thing is that, if i understand you correctly, you want to get a range which goes up to the last date in the sales table. Either 1, 2, 3 etc weeks back from this date and you want the chart to dynamically adjust to show the range on the x-axis.

However, the LASTDATE function in the current measure will take on a different value for each date value on the x-axis.  You could probably see this quite clearly by showing the data in a table.  I think the measure at the moment produces a kind of rolling sales total(if that's what [switch sales] is)  going back n weeks for any sales date.  It works in a card because there won't be a series of date values.

You need to find a way of telling the measure that LASTDATE should refer to the last sales date.  I'm going to take a bit of a flyer and say that putting ALL() round the Sales Date in the LASTDATE clause will get you this.

And the last part is, how is that LastN picked up from the slicer?  Because there's no SELECTEDVALUE or other function wrapping the call.

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors