Full year turnover

[brinky]

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.

[amitchandak]

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

[brinky]

@amitchandak Thanks, will try your measures as well, will keep you updated.

[Mariusz]

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]

@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]

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.

[brinky]

@Mariusz 

[Mariusz · Accepted solution]

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.

[brinky]

@Mariusz Thanks it's just what I need.

Just asking not to bother you further, could I use such filter in the measure created?

FILTER(CoffeeDB,CoffeeDB[Brand]<>BLANK()

Thanks for you time & good day.

Stephen

[Mariusz]

Hi @brinky 

Sure, you should be able to add it to the calculate as an extra argument like below

Last Sales Year = 
VAR _ly = CALCULATE(
    MAX( 'Calendar'[Year] ) - 1,
    ALL( 'Calendar' ),
    Sales
)
RETURN 
CALCULATE( 
    [Sales],
    INT( 'Calendar'[Year] ) = _ly,
    FILTER( CoffeeDB, CoffeeDB[Brand] <> BLANK() )
)

Best Regards,
Mariusz

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

[brinky]

@Mariusz Thanks agian, but I'm getting the following error