Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Update on formula - Year

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
Need Help
Show and Tell
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    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 )
        )
    )

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    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 )
        )
    )
  • Gokul_G16's avatar
    Gokul_G16
    Resolver I
    3 years ago

    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