Filter between dates for a Summed Quantity- Rolling 30 days

[ModernAchilles]

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:

[v-juanli-msft · Accepted solution]

Hi @ModernAchilles

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

[ModernAchilles]

Thanks Maggie!

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

Thanks so much!

[ModernAchilles]

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!

[v-juanli-msft]

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

Best Regards

Maggie

[ModernAchilles]

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

[v-juanli-msft]

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

Best Regards

Maggie

[Ola_S]

This might help: https://youtu.be/qiLwt3Pmxv8?list=PL1myWUzvmmDGmLfty3BDluz8nzme1dZxg&t=149

[AlB]

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

Hi @ModernAchilles

Can you sahre the pbix?

[ModernAchilles]

Ha Sure how do I do that?