Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 sa...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

amitchandak's avatar
amitchandak
Super User
3 years ago

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