Current year blank values

[Anonymous]

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))

[v-yangliu-msft · Accepted solution]

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

[MahyarTF]

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

[Anonymous]

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

[MahyarTF]

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)

[amitchandak]

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