Dax for Date : Calculate From a specific month to last month

[cristianml]

Hi,

I would like to modify the following formula until LAST MONTH (as restriction) instead of using a Slicer (FilterYtd) :

YTD Actual Cost =

CALCULATE (

[Actual Costs],

VAR FirstFiscalMonth = 9 -- Set the first month of the fiscal year

VAR LastDay =

MAX ( 'List Period'[Date Period] )

VAR LastMonth =

MONTH ( LastDay )

VAR LastYear =

YEAR ( LastDay )

- IF ( LastMonth < FirstFiscalMonth, 1 )

VAR FilterYtd =

DATESBETWEEN (

'List Period'[Date Period],

DATE ( LastYear, FirstFiscalMonth, 1 ),

LastDay

)

RETURN

FilterYtd

Could you help me ?

Thanks. 

[edhans]

Can you post a working formula? That one doesn't work. I'm not an expert on variables, but don't think you can create variables inside of a CALCULATE() function, or any other function.

---

[cristianml]

This formula works fine, but just from Sept2018 to the filtered date I choose from Slicer. The idea is to modify this to show till Last month only : 

[cristianml]

Hi @edhans ,

This formula works fine, but just from Sept2018 to the filtered date I choose from Slicer. The idea is to modify this to show till Last month only. 

Thanks.

[edhans]

---

@cristianml wrote:

Hi @edhans ,

This formula works fine, but just from Sept2018 to the filtered date I choose from Slicer. The idea is to modify this to show till Last month only. 

Thanks.

---

Now it works. Your OP was missing the final ")" so I got an error playing with it.

I'm still not sure exactly what you are asking, but the following will return the last day of the previous month, so you could use this in a variable and use it as your LastDay or wherever it needs to go in that measure.

LastDayOfPreviousMonth = 

CALCULATE(
    EOMONTH(
        ENDOFMONTH(Dates[Date]),
        -1),
    FILTER(Dates, Dates[Month] = MONTH(TODAY()))
)

[cristianml]

Hi @edhans ,

Thanks for your response. But let me explain myself better. In this case what I need is to calculate my measure "[Actual Costs]"  Between Sep18 and April19  (without requiring or using Slicers)

So, for Sep18 I Should use a VAR like is in the  formula : VAR FirstFiscalMonth = 9 -- Set the first month of the fiscal year

and last month with somenthing like this :  Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1) 

is possible to calculate a measure with this 2 variables ?

Thanks !

[edhans]

Why not just use DATESBETWEEN() as a filter?

Range of Units = 
    CALCULATE(
        [Total Quantity],
        DATESBETWEEN(
            'Date'[Date],
            DATE(2018,9,1),
            EOMONTH(TODAY(),-1)
        )
    )

You don't need to use a variable here. You can, but it doesn't really provide much in the way of readabity with such a simple measure, and doesn't give any of the benefits variables can give to avoid the use of EARLIER() functions.

[cristianml · Accepted solution]

Hi @edhans

Finally I realized that I had to change 1 line only. Leaving the following formula:

YTD Actual Cost =

CALCULATE (

[Actual Costs],

VAR FirstFiscalMonth = 9 -- Set the first month of the fiscal year

VAR LastDay =

MAX ( 'List Period'[Date Period] )

VAR LastMonth =

MONTH ( LastDay )

VAR LastYear =

YEAR ( LastDay )

- IF ( LastMonth < FirstFiscalMonth, 1 )

VAR FilterYtd =

DATESBETWEEN (

'List Period'[Date Period],

DATE ( LastYear, FirstFiscalMonth, 1 ),

EOMONTH ( TODAY (), -1 )

)

RETURN

FilterYtd

)

regards