End of the Month Previous Year

[cecitorresHF]

Hola 

Tengo una tabla de ventas que se actualiza diario, esta tabla contiene la venta actual (dia a dia), venta año pasado (usando la funcion SAMEPERIOD) y necesito una medida mas que me indique la venta total al final del mes pero del año anterior.

Alguna sugerencia para esta medida?

[Icey]

Hi @cecitorresHF ,

How about this:

Sales of Last Year Month = 
VAR ThisYear =
    YEAR ( MAX ( 'Table'[Date] ) )
VAR ThisMonth =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR LastYearMonthStart =
    DATE ( ThisYear - 1, ThisMonth, 1 )
VAR LastYearMonthEnd =
    EOMONTH ( LastYearMonthStart, 0 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] >= LastYearMonthStart
                && 'Table'[Date] <= LastYearMonthEnd
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cecitorresHF]

Hola 

Esta vez no me da ningun valor.

aqui te pongo un  screenshot de mis tablas 

[Icey]

Hi @cecitorresHF ,

Please modify your measure like so:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cecitorresHF]

Aun no me muestra ningun valor.

[Icey]

Hi @cecitorresHF ,

What is your data model? Is there a special part? Could you create a sample .pbix or just some sample data for me to test? No need for real data, just want to understand your scenario more clearly.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

[cecitorresHF]

Tengo dos tablas, no estoy segura como pasarte un link aqui pero te explico como son.

Calendara table:

Contiene una columna con un listado de fechas 

Sales Table 

Contiene tres columnas, Branch, Date, Total

Ambas tablas estan relacionadas en base a la fecha. Calendar table [Date] ----> Sales Table [Date]

[Icey · Accepted solution]

Hi @cecitorresHF ,

I find that there are no Date column in your Matrix visual. Therefore I guess you have a date slicer to select one month. Right?

If my guess is correct, try to change the measure like so:

Sales of Last Year Month = 
VAR ThisYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
VAR ThisMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR LastYearMonthStart =
    DATE ( ThisYear - 1, ThisMonth, 1 )
VAR LastYearMonthEnd =
    EOMONTH ( LastYearMonthStart, 0 )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            ALLEXCEPT('Sales',Sales[Branch] ),             ----------use ALLEXCEPT
            'Sales'[Date] >= LastYearMonthStart
                && 'Sales'[Date] <= LastYearMonthEnd
        )
    )

Or this:

Sales of Last Year Month =
VAR ThisYear =
    YEAR ( MAX ( 'Calendar'[Date] ) )
VAR ThisMonth =
    MONTH ( MAX ( 'Calendar'[Date] ) )
VAR LastYearMonthStart =
    DATE ( ThisYear - 1, ThisMonth, 1 )
VAR LastYearMonthEnd =
    EOMONTH ( LastYearMonthStart, 0 )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Branch] = MAX ( Sales[Branch] )
                && 'Sales'[Date] >= LastYearMonthStart
                && 'Sales'[Date] <= LastYearMonthEnd
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @cecitorresHF ,

Does this work?

Best Regards,

Icey

[amitchandak]

@cecitorresHF , if current month July, then the last Dec is 7/8 months awa. depending on formula we use 

last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*(month(Today())+1) ,MONTH)))

Measure = var _max = maxx(ALLSELECTED('Date'), 'Date'[FY Month]) +1 // remove +1 if needed
return
CALCULATE([Sales],DATESMTD(DATEADD('Date'[Date],-1*_max,MONTH)))

or

last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-1*month(Today()),MONTH)))

[cecitorresHF]

Hola 

Utilice la formula que me proporcionaste pero me calcula la venta total de un mes diferente, en este momento estoy intentando calcular las ventas de julio 2021(actual), mismo periodo año pasado y total julio 2020, sin embargo la formula me calcula el total para noviembre 2020