Update on formula - Year

[Anonymous]

Hello Team,

I usually show sales of the previous month with the below expression:

Sales-1 = Calculate(

    Sum(Sales_Main[Price_Excl_VAT]),

    Month(Sales_Main[Date_Vente])=Month(Today())-1,

    Year(Sales_Main[Date_Vente])=Year(Today()))

With this new year, the formula does not work anymore as we are in 2023.

Any idea of how I can get it to work?

Thank you.

Kind Regards,

Hasvine

[Gokul_G16]

Hi @Anonymous ,

I will do one thing for my suggestion.

step 1  : Datemaster table.
VAR _MAXDATE=MAX(datemasterdate)
VAR _Max= calculate (DateADD(_MAXDATE,month,-1))
step 2=

Sales-1 = Calculate(

    Sum(Sales_Main[Price_Excl_VAT]),

    Month(Sales_Main[Date_Vente])<=_Max,
     All(Datemaster Date)).

Kindly check and update me.

Thanks.
Gokul

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

Please try below code:-

Sales-1 =
VAR _prev_month =
    MONTH ( EOMONTH ( TODAY (), -1 ) )
VAR _prev_year =
    YEAR ( EOMONTH ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sales_Main[Price_Excl_VAT] ),
        FILTER (
            Sales_Main,
            INT ( MONTH ( Sales_Main[Date_Vente] ) ) = INT ( _prev_month )
                && INT ( YEAR ( Sales_Main[Date_Vente] ) ) = INT ( _prev_year )
        )
    )