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

MahyarTF's avatar
MahyarTF
Memorable Member
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    MahyarTF
    Memorable Member
    3 years ago

    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)