Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm wondering if it's possible to adjust this measure to change dynamically instead of changing the year manually?
Solved! Go to 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
)
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
Hi @brinky
Try this
LY =
CALCULATE(
SUM( '04 Fact'[turnover] ),
SAMEPERIODLASTYEAR( '01 dDate'[Date] )
)
@Mariusz Thanks, I did try but it's not giving the full year sales data, would like the full year for 2018 (Jan-Dec)
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
)
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
)
@Mariusz Thanks it's just what I need.
Just asking not to bother you further, could I use such filter in the measure created?
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() )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |