Forum Discussion

EktaSoni_R's avatar
EktaSoni_R
Icon for Helper I rankHelper I
2 years ago
Solved

Cummulative Sales calculation only for previous year.

Hello, 
I have tried to calculated only previous year cumulative sales, But unable to achive, i have tried n number of formulas but i am unable to get the output. Below dax is giving me till todays month data , i want restrict only for the last year. Any lead will be much appreciated.

LastYearAccumulativeRevenue =
CALCULATE(
SUM([amount]),
YEAR('Date'[Date]) = YEAR(TODAY())-1,
'Date'[Date]<= MAX('Date'[Date])
)

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    2 years ago

    EktaSoni_R 

    Please try this:

    YTD LY = 
    IF( YEAR( MAX( 'Date'[Date] ) ) = YEAR( TODAY()),
    
        CALCULATE(
            CALCULATE(
                [Sales Amount],
                DATESYTD( 'Date'[Date] )   
            ),
            SAMEPERIODLASTYEAR( 'Date'[Date] )
        )
    )

10 Replies

  • philouduv's avatar
    philouduv
    Icon for Resolver III rankResolver III
    2 years ago

    Hello EktaSoni_R,
    I tried a little calculated column in a fake table that had 2 column, amount and date. 

    Amountdate

    104/12/2023
    226/08/2023
    318/05/2023
    407/02/2023
    530/10/2022
    622/07/2022
    713/04/2022
    803/01/2022
    925/09/2021
    1017/06/2021


    Hope it helps, if not you would need to give further details.
    Formula: 

    cumulative_sum_previous_year = CALCULATE(SUM(Table9[Amount]),
     Filter(Table9,
     Table9[date].[Year] < MAX(Table9[date].[Year])
     &&
     Table9[date].[Year] > MAX(Table9[date].[Year]) - 2
     ))
     

    Best regards,

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    EktaSoni_R 

    Folow this pattern:

    YTD LY = 
CALCULATE(
    CALCULATE(
        [Sales Amount],
        DATESYTD( 'Date'[Date] )   
    ),
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)
    • EktaSoni_R's avatar
      EktaSoni_R
      Icon for Helper I rankHelper I
      2 years ago

      Fowmy 
      Above Dax is giving correct output while testing using the table view with date slicer, but the problem is when same i am using in Bar chart without any slicer, it is giving the all previous year data which is present in date table.

       My current requirement is there wont be any slicer for date, chart shows only for previous year cummulative value.

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        2 years ago

        EktaSoni_R 

        I am not sure how you have used the measure on the bar chart. However, the basic idea is that you need to have a date in the x-axis it could be month, qtr or year, in the Y-axis add the this measure.