cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Last Month's Data

Hi,

I've found that I can use the following to identify a rolling prior 1 month period:

Present Month Volume = CALCULATE(SUM('Totals'[USD Total Amount]),
DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))

The issue is that my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month.  How should I adapt the formula to deliver the prior month's result?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

## Re: Last Month's Data

my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month.

What exact result are you expecting? Is the “prior month’s result” refers to the results from “today” subtracting one month? (So the final day is “today”).

As I tested, I have got a trade date column like:

Referring to your DAX, the final result becomes (the final day is 2017-12-31)

So I assume it is not the result set you want. (screenshot above)

Hence, as a quick guess, I suggest you replace the

DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))

With

DATESINPERIOD('Totals'[Trade Date], TODAY(), -1, MONTH)

Then I get result ended up “today()”

Regards,

Charlie Liao

2 REPLIES 2
Highlighted
Moderator

## Re: Last Month's Data

my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month.

What exact result are you expecting? Is the “prior month’s result” refers to the results from “today” subtracting one month? (So the final day is “today”).

As I tested, I have got a trade date column like:

Referring to your DAX, the final result becomes (the final day is 2017-12-31)

So I assume it is not the result set you want. (screenshot above)

Hence, as a quick guess, I suggest you replace the

DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))

With

DATESINPERIOD('Totals'[Trade Date], TODAY(), -1, MONTH)

Then I get result ended up “today()”

Regards,

Charlie Liao

Regular Visitor

## Re: Last Month's Data

Many thanks Charlie, that works.

## Helpful resources

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 297 members 3,046 guests
Recent signins:
Please welcome our newest community members: