Sales last 28 days, ignoring filter

DAX DAX Calculations
1

Hi there,

I am struggling with something I know should be simple…

Please see attached sample demonstrating the issue:Orders Last 28 Days.pbix (288.9 KB)

My report has a column on the date table called ’ Current Period’ which stamps all date records belonging to the current month with Y, or the prior month if the 1st of the month. So on 1st Aug 2020, it will automatically return all dates for July 2020.

I have a [Total Sales] measure which simply returns the count of orders. So with the ‘Current Period’ set to Y, I see my sales for July 2020:

image
image329×726 17.9 KB

However I also need a table, which contains the [Total Sales] not for the current period, but for the Last 28 Days from today. I have therefore created a new measure [Orders Last 28 Days] and tried many ways to get it to return the sales on dates between June 15th and today, July 13th, thereby ignoring the [Current Period] filter, but I just can’t get it to work.

My last attempt, measure [Orders Last 28 Days 2], uses Sam’s example in this video,

image
image897×382 64.2 KB

Whilst this does give me the correct total count of orders (4614), it does not allow them to be reported against each date record (15th June to July 13th) in my table.

Any help greatly appreciated as always!

Thanks

Mark

2

Hi @Mark,

You are filtering the table by the currentPeriod column, so you won’t be able to see others dates in your table visual, only access the values and use them the dates returned by the drop down filter.

Check the file: Orders Last 28 Days.pbix (296.3 KB)

I’ve create a new table with the values “Y” and “N” for the column currentPeriod and named this table as
DynamicMeasure. Also I’ve created a new measure for the Total Orders:

_Total Orders 2 = IF(SELECTEDVALUE(DynamicMeasure[CurrentPeriod], “”) = “Y”,
CALCULATE([Total Orders], Dates[CurrentPeriod] = “Y”),
CALCULATE([Total Orders], Dates[CurrentPeriod] = “N”)
)

I got this result:

Capture
Capture1313×789 29.4 KB

The measure for the 28 previous days from today is:

_Total Orders 28 days =
VAR _dates = DATESINPERIOD(Dates[Date], TODAY(), -29, DAY)
RETURN CALCULATE(COUNTROWS(Orders), FILTER(Dates, Dates[Date] in _dates))

The point here is to have a disconnected table to have access to all dates and filter them using measures.

Ricardo

3

Hi @Mark, did the response provided by @ricardocamargos88 help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!

5

Hi @ricardocamargos88

Apologies for my delayed response to your reply…

Thank you very much for the clear answer, it really has helped. I had not understood that I would be unable to visualise these dates which were being excluded by the filter with the the use of ALL. I now understand that ALL will allow me to access these values only.

I like the idea of the disconnected table and will use this in my report.

Thanks again,

Mark

T