cancel
Showing results for
Did you mean:
Helper I

## Filter between dates for a Summed Quantity- Rolling 30 days

Hi,

I am trying to sum the qty sold from all transactions within a certain time frame. Starting with today and looking back the last 30 days of transactions. There are multiple transactions of each product on each date.

I put this formula together, but will only return something is I put -400 instead of -30. So I'm not sure what it is grabbing.

I also looked at the datesbetween formula, but that didnt work either dynamically with today(). Is there a better way to do this? Thanks!

My Date Data looks like this:

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Modify with this measure

```rolling-30days =
CALCULATE (
[measure-sales],
DATESINPERIOD ( Sheet2[date], TODAY (), -30, DAY ),
FILTER ( ALL ( Sheet2 ), [date] <= MAX ( [date] ) )
)
```

If "today()" doesn't fit your scenario,

please create a measure with "today()","utcnow()","now()",

then share the screenshot with me to let me know the time zone difference between yours and mine,

finally i can provide more sufficient solution for you.

Best Regards

Maggie

10 REPLIES 10
Super User III

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Can you sahre the pbix?

Helper I

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Ha Sure how do I do that?

Frequent Visitor

Super User III

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

@ModernAchilles

You can share the URL to the file. Either from a platform like OneDrive, Dropbox, etc. or you can upload the file to a site like this (no sign-in required) or this

Community Support

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

I make a test with your first measure, it shows the last date of the end of the year in the table.

This may be why it only returns something is I put -400 instead of -30

Best Regards

Maggie

Community Support

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Modify with this measure

```rolling-30days =
CALCULATE (
[measure-sales],
DATESINPERIOD ( Sheet2[date], TODAY (), -30, DAY ),
FILTER ( ALL ( Sheet2 ), [date] <= MAX ( [date] ) )
)
```

If "today()" doesn't fit your scenario,

please create a measure with "today()","utcnow()","now()",

then share the screenshot with me to let me know the time zone difference between yours and mine,

finally i can provide more sufficient solution for you.

Best Regards

Maggie

Helper I

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Thanks Maggie!

That is a great formula for me it Worked perfect. I really appreciate it. 🙂

Thanks so much!

Helper I

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Hi Maggie, @v-juanli-msft How would I add a filter that if its blank don't return it to this? I have some products that haven't been sold in the last 30 days showing up in the report id like to exclude them. Thanks again!
Community Support

## Re: Filter between dates for a Summed Quantity- Rolling 30 days

Is this what you want?

`rolling-30days =IF ( MAX ( [sales] ) <> 0, CALCULATE ( [measure-sales], DATESINPERIOD ( Sheet2[date], DATE ( 2019, 1, 7 ), -30, DAY ), FILTER ( ALL ( Sheet2 ), [date] <= MAX ( [date] ) ) ))`

Best Regards

Maggie

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors