Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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

Thanks Maggie!

 

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

 

Thanks so much!

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!

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

Hi Maggie,

 

Thanks for all your help on this. I would like to understand how this formula can work.  🙂

 

I changed the dates to have minutes and seconds on it and now it seems to not work. Also, the blanks are still there and not populated with "0" with this add on. 

 

My Time Zone is Denver, Colorado USA

 

 

 

Rolling Taken out blanks.PNG

 

 

 

v-juanli-msft
Community Support
Community Support

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

Ola_S
Frequent Visitor

@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  

AlB
Super User
Super User

Hi @ModernAchilles

 

Can you sahre the pbix?

Ha Sure how do I do that?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.