Upper an Lower Limits changing the date range on my graph

[jbrijalba]

I have a table that is called Limits with two fields in it, Amount and Purchase date. In Edit Queries I limited the data load to only allow the past 18 months of data to be loaded in. I set up a line and bar chart to show spend by month and year (pulled the date heirarchy in and removed quarter and day, then expanded one level down the heirarchy). 

I then created a measure to make a Upper Limit.

My equation is: =Calculate(Average('Limits'[Amount]), All(Limits))

                                     + (3 * Calculate(STDEV.P('Limits'[Amount]),All(Limits)))

What happens is my graph now shows all the months for the years that are met by the original data load filter. For a better picture, it is March 2020 so my graph should show October 2018 - March 2020. But instead it is showing Jan 2018 - December 2020. 

I know what is happening but I am not sure how to fix. I've tried many date filters but have not found a good dynamic way to filter the equation to get the desired result or the past 18 months spend with an upper and lower constraint.

[v-lionel-msft]

Hi @jbrijalba ,

Maybe the ALL () function removed the filters. But, when I did the test, nothing you described happened.

"In Edit Queries I limited the data load to only allow the past 18 months of data to be loaded in."

How did you do it ?

I did it like this:

Add a custom filter.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jbrijalba]

@v-lionel-msft  The ALL function should only remove the filters on the visual/page/report levels not the data load level, since the data load filter removes all other months except the desired rolling 18 months. That is my understanding of the ALL function, if that is correct, then any measure created should only take into account the dates that are allowed in by the date filter in Edit Queries.

As far as my filter I did the previous 17 months or this month as my date filters. 

How did you do your date filter? I need this to be dynamic if at all possible since this will be updated monthly and I do not want to have  to manually change filters for my client.

Thank you.

[v-lionel-msft]

Hi @jbrijalba ,

Yes, the ALL() function doesn't work when you use "Filter Rows" feature.

So, the problem you met is so strange, are you sure that the "Filter Rows" feature work? Please check the fact table in Data View.

Or would you mind sharing your .pbix file for a look?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jbrijalba]

Wish I could share my file but it is a clients data and I have signed a confidentiality agreement. 

As you can see, the date filter in Edit Queries is working. It seems to be the ALL function is including all of the months for the years that the date filter is bringing in. Ex: the date filter excludes Jan 2018, however the measure created for the limit is taking into account jan 2018. 

I have tried using DatesBetween, DatesInPeriod but they either do not work or make the limit lines disappear.

[v-lionel-msft]

Hi @jbrijalba ,

I can't reproduce your problem, please create support ticket .

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jbrijalba]

@v-lionel-msft  Try adding spend amount as a column to the graph (the graph I am using is a combo column and line graph). 

Also how are you bringing your date to the axis? I am adding it as a hierarchy, removing day and quarter then clicking the Expand all down one level in the hierarchy button. Maybe that has something to do with it.

Thank you.

[jbrijalba · Accepted solution]

@v-lionel-msft Can confirm, it was the date hierarchy I added in. I brought the date field as the actual date (not hierarchy) and it fixed the issue. 

[jbrijalba]

Changed my equation to UCL = Calculate((Average(Limits[Amount]) + (3 * STDEV.P(Limits[Amount]))), All(Limits))

Makes it easier to filter since you only have to filter once.