Forum Discussion
Current year blank values
In data there is a year 2021 only. i am trying to do analysis last and current year
Current 2022 year is going .. and when i try this measure this shows that
current year sales 0
last year sales 50.86 M
whereas according to data current year is 2021 .. so this should be
current year 50.86
last year sales 0
how i do this
i tried these expression
Current_year_sales = CALCULATE(SUM(Data[Net Sales]),FILTER(Data,Data[Year]=MONTH(TODAY())))
last_year_sales = var current_year= YEAR(TODAY()) return CALCULATE(SUM(Data[Net Sales]),FILTER(Data,Data[Year]=current_year -1))
Hi Anonymous ,
I created some data:
2020.1.1 – 2021.12.31
Here are the steps you can follow：
1. Create measure.
last year sales = var _maxdate= MAXX(ALL('Table'),[Date]) return IF( SUMX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(_maxdate)-1),[Column])=BLANK(),0)current year = var _maxdate= MAXX(ALL('Table'),[Date]) return SUMX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(_maxdate)),[Column])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Anonymous , With help from separate date or year table
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Based on today
This year Today =
var _min = eomonth(today(),-1*month(today()))+1
var _max = eomonth(_min,11)
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Last year Today =
var _max = eomonth(today(),-1*month(today()))
var _min = eomonth(_max,-12)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Hi,
If the maximum date is for the last year(2021), you could use the Max(date) against the Today().
When you use Month(Today()) in your DAX, the 2022 is the result for current year.
Appreciate your Kudos and please mark it as solution if it helps
MahyarTF ok i did this but this shows "blank" where as i want if amount is 0 then i want to show 0 instead of BLANK .. i tried this
last_year_sales = var current_year= YEAR(MAX(Data[Date Main])) return CALCULATE(SUM(Data[Net Sales]),FILTER(Data,Data[Year]=current_year -1))Hi,
If the issue for the current year is fixed, for the blank value you could use the if(isblank(...),0,...) in your dax like below :
Curreny_Year_sales =Var Curr_Sales = CALCULATE(sum(Sheet211[Sales]), filter(sheet211, Sheet211[Date].[Year] = year(TODAY())))return if(ISBLANK(Curr_Sales), 0, Curr_Sales)
Hi Anonymous ,
I created some data:
2020.1.1 – 2021.12.31
Here are the steps you can follow：
1. Create measure.
last year sales = var _maxdate= MAXX(ALL('Table'),[Date]) return IF( SUMX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(_maxdate)-1),[Column])=BLANK(),0)current year = var _maxdate= MAXX(ALL('Table'),[Date]) return SUMX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(_maxdate)),[Column])2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
