Forum Discussion

brinky's avatar
brinky
Icon for Helper IV rankHelper IV
6 years ago
Solved

Full year turnover

Hello,

I'm wondering if it's possible to adjust this measure to change dynamically instead of changing the year manually?

 

2018 = CALCULATE(SUM('04 Fact'[turnover]),FILTER('01 dDate','01 dDate'[Year]="2018"))
 
Please note that my data is 1 month in behind so today is the 12th November 2019, in my model I have only data till 31st October 2019, as my data updates on monthly basis.
 
 

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi brinky 

    If your Year column is a Text Data Type then you can wrap it with VALUE() or INT( ) like below, this should clear the error.

     

    Last Sales Year = 
VAR _ly = CALCULATE(
    MAX( 'Calendar'[Year] ) - 1,
    ALL( 'Calendar' ),
    Sales
)
RETURN 
CALCULATE( 
    [Sales],
    INT( 'Calendar'[Year] ) = _ly
)

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

10 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi brinky 

     

    Try this

     

    LY = 
CALCULATE(
    SUM( '04 Fact'[turnover] ),
    SAMEPERIODLASTYEAR( '01 dDate'[Date] )
)

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.
    • brinky's avatar
      brinky
      Icon for Helper IV rankHelper IV
      6 years ago

      Mariusz Thanks, I did try but it's not giving the full year sales data, would like the full year for 2018 (Jan-Dec)

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi brinky 

         

        Try the below, if its what you need.

        VAR _ly = CALCULATE( 
    MAX( '01 dDate'[Year] ) - 1,
    ALL( '01 dDate' ),
   '04 Fact'
)
RETURN 
CALCULATE(
    SUM( '04 Fact'[turnover] ),
    '01 dDate'[Year] = _ly
)

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try

    Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),('Date'[Date Filer])))
Last Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),dateadd('Date'[Date Filer].-12,month)))

    Or force till last month

    Year  = CALCULATE(SUM(table[column]),DATESYTD(endofmonth(dateadd('Date'[Date Filer],-1,MONTH))))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(endofmonth(dateadd('Date'[Date Filer],-13,MONTH))))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),endofmonth(dateadd('Date'[Date Filer].-1,month))))
Last Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),endofmonth(dateadd('Date'[Date Filer].-13,month))))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601