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
wooand
Helper II
Helper II

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
v-caliao-msft
Employee
Employee

@wooand,

 

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:
Capture1.PNGCapture2.PNG

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

Capture3.PNG


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()”
Capture4.PNG

 

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@wooand,

 

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:
Capture1.PNGCapture2.PNG

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

Capture3.PNG


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()”
Capture4.PNG

 

Regards,

Charlie Liao

Many thanks Charlie, that works.

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.