cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ModernAchilles Helper I
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!

 

 

 

LAst 30.PNG

 

 

My Date Data looks like this:

 

 

Datadate.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

Hi @ModernAchilles

Modify with this measure 

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

3.png

 

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

View solution in original post

10 REPLIES 10
Super User III
Super User III

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

Hi @ModernAchilles

 

Can you sahre the pbix?

ModernAchilles Helper I
Helper I

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

Ha Sure how do I do that?

Ola_S
Frequent Visitor

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

Super User III
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
Community Support

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

Hi @ModernAchilles

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

2.png

 

 

 

Best Regards

Maggie

Community Support
Community Support

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

Hi @ModernAchilles

Modify with this measure 

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

3.png

 

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

View solution in original post

ModernAchilles Helper I
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!

ModernAchilles Helper I
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
Community Support

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

Hi @ModernAchilles

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] ) )
)
)

9.png

 

Best Regards

Maggie

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors