DAX YTD N-1 EMONTH

[Sylvine_Wyz]

Hello everybody, 

I want to know the total of quantity about the last year until the last month.
Ex : if we are the 15th april 2021, i want the total of quantity from 01/01/2020 to 31/03/2020
If we are the 1st may 2021, i want the total of the quantity from 01/01/2020 to 30/04/2020.

Currently, i use this formul ant it s correct : 

formul =

var _max = eomonth(date(year(today())-1,month(today()),day(today())),-1)

return

CALCULATE(Sum('wyz_reporting bi_order'[quantity_order_line]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

//CALCULATE(Sum('wyz_reporting bi_order'[quantity_order_line]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

But if in my report, i choose the segment "February", i want to appair the quantity of february only. Currently it's the sum of january and february. 

Thanks in advance for your help

Have a nice day

[v-luwang-msft · Accepted solution]

Hi @Sylvine_Wyz ,

If slicer is date，you could try the following measure:

formul =
VAR _max =
    EOMONTH (
        DATE ( YEAR ( TODAY () ) - 1, MONTH ( SELECTEDVALUE ( 'date'[Date] ) ), DAY ( SELECTEDVALUE ( 'date'[Date] ) ) ),
        -1
    )
VAR _min =
    DATE ( "2020", "1", "1" )
VAR TEST =
    CALCULATE (
        SUM ( 'wyz_reporting bi_order'[quantity_order_line] ),
        FILTER (
            'wyz_reporting bi_order',
            'wyz_reporting bi_order'[Date] >= _min
                && 'wyz_reporting bi_order'[Date] <= _max
        )
    )
RETURN
    TEST
//CALCULATE(Sum('wyz_reporting bi_order'[quantity_order_line]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)))))))))))

If slicer is month，you could try the following measure:

formul2 =
VAR YEAR =
    SELECTEDVALUE ( 'date'[Year] )
VAR mon =
    SELECTEDVALUE ( 'date'[mon] )
VAR _max =
    EOMONTH ( DATE ( YEAR - 1, mon, "01" ), -1 )
VAR _min =
    DATE ( "2020", "1", "1" )
VAR TEST =
    CALCULATE (
        SUM ( 'wyz_reporting bi_order'[quantity_order_line] ),
        FILTER (
            'wyz_reporting bi_order',
            'wyz_reporting bi_order'[Date] >= _min
                && 'wyz_reporting bi_order'[Date] <= _max
        )
    )
RETURN
    TEST

base data:

And final result:

You could download my pbix file if you need!

Wish it is helpful for you!

Best Regards

Lucien

[Sylvine_Wyz]

@amitchandak Hello Thanks for your return.
I tried the mesure.
If a month is seleted it's correct but if no month is selected, the quantity is the total of 2020 and not until 31/03/2020

[amitchandak]

@Sylvine_Wyz , You can try measure M1 and M3

M1 = CALCULATE(Sum('wyz_reporting bi_order'[quantity_order_line]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

M2= CALCULATE(Sum('wyz_reporting bi_order'[quantity_order_line]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

M3 = if(isfiltered(Date[Month]) , [M1], [M2])