Forum Discussion

wooand's avatar
wooand
Helper II
8 years ago
Solved

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], ...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    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:


    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

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
8 years ago

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:


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

  • wooand's avatar
    wooand
    Helper II
    8 years ago

    Many thanks Charlie, that works.