Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI Total

Hi, I have created measures for Current Year Sales vs Last Year Sales.  For Current Year: CY Sales = SUM(sales[subtotal])   For Last Year: LY Sales = CALCULATE([Sales], 'sales'[Year] = S...
Tutorial Requests
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous  , 

    Every row has context for measure to calculate. So, we should modify the context via filter(all(table)....). at this point, we need condsider each month but also the total row. So get a range date of current row. For example, month 1 year 2021 , the start date of last year is date(2021 -1 ,1,1), the end date is date(2021-1,1,31). This is what var _start and var _end do.

     

    But if your "startdate" type is Date, maybe you can try this code:

    LY sales = CALCULATE(SUM('Table'[Sales]),all(),DATEADD('Table'[Date],-1,YEAR))​

    result:

     

    You can refer the pbix in the attachment. if you need more help, please share your pbix file without sensitive date.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Using SELECTEDVALUE inside a CALCULATE is asking for trouble.  You will need to add a "IsPastInPreviousYear" flag to your calendar table to indicate which timeframe should be considered. Then the measure would be 

 

LY Sales = CALCULATE([Sales], SAMEPERIODLASTYEAR('Dates'[Date]),'Dates'[IsPastInPreviousYear])

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    Thank you for your response.

    I do not have date table, my dates are in same sales table. I tried to use this measure but it doesn't work either.

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous ,

       

      Is that you only have one sale table?

      If yes, it is strange that LY Sales won't get the result when you select 2022 unless the [Sales] has some function like all() or removefilter() in its code.

       

      If I'm wrong, you can try using the following measure.

      LY Sales =
VAR _start =
    DATE( MAX( sales[year] ) - 1, MIN( 'sales'[month] ), 1 )
VAR _end =
    DATE( MAX( 'sales'[year] ) - 1, MAX( 'sales'[month] ) + 1, 1 ) - 1
RETURN
    CALCULATE(
        SUM( 'sales'[sales] ),
        FILTER( ALL( table ), [dates] >= _start && [dates] <= _end )
    )

      If you do not have [dates] in sales table, please create dates column by [year], [month], [day]

       

      Or please share sample data and expect result, if you need more help.

       

      Best Regards

      Community Support Team _ chenwu zhu

       

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