Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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
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
Thanks Maggie!
That is a great formula for me it Worked perfect. I really appreciate it. 🙂
Thanks so much!
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
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
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
Ha Sure how do I do that?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |