Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX for lastyear data

Hello All, 

this is the table i have

NameBalExpFy
a910

2023

a15202024
a10152025

 

the requirement is that i need to create a measure or a column which will give me balance total (last years bal + last years Exp)

so if I select Fy 2023 then i should get the below table

balance total is 0 because we don't have any data for last year

NameBalExpbalance total
a910

0

now if we select Fy2024 i need to get the below

balance total is 19 (last year bal + last year Exp )

NameBalExpbalance total
a1520

19

 

Need Help
Tips and Tricks
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    here comes the verified code for calculated column:

    BalanceTotal = 
VAR _pre =
FILTER(
    tableName,
    tableName[name]=EARLIER(tableName[name])
         &&tableName[fy]=EARLIER(tableName[fy])-1
)
VAR _balpre =
MINX( _pre, tableName[Bal])
VAR _exppre =
MINX(_pre, tableName[Exp])
RETURN
_balpre + _exppre

     

    And result:

     

     

7 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    Anonymous ,

    Can you try this measure?

     

    Bal + Exp Last Year = 
VAR _selectedYear =
    IF ( HASONEVALUE ( Test[Fy] ), FIRSTNONBLANK ( Test[Fy], Test[Fy] ), BLANK () ) - 1
RETURN
    IF (
        CALCULATE ( SUMX(Test,Test[Bal]+Test[Exp]), ALL ( Test ), Test[Fy] = _selectedYear ) = 0,
        0,
        CALCULATE ( SUMX(Test,Test[Bal]+Test[Exp]), ALL ( Test ), Test[Fy] = _selectedYear )
    )

     

    Thanks,

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    here comes the verified code for calculated column:

    BalanceTotal = 
VAR _pre =
FILTER(
    tableName,
    tableName[name]=EARLIER(tableName[name])
         &&tableName[fy]=EARLIER(tableName[fy])-1
)
VAR _balpre =
MINX( _pre, tableName[Bal])
VAR _exppre =
MINX(_pre, tableName[Exp])
RETURN
_balpre + _exppre

     

    And result:

     

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Create a new table with distinct year and join it with the year of your table, Say the table name is date 

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sumX('Table', [Balance] + [Exp]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))


    Last Year = CALCULATE(sumX('Table', [Balance] + [Exp]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello There,

      Thanks for your response

      the requirement is that i create one measure which will show the balance total

      in the slicer for year if i select 2023 then the balance total is 0 if i select any other year then the total is addition of last years bal and exp

       

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    you might create a column like this

    BalanceTotal =
    VAR _pre =
    FILTER(
        tableName,
        tableName[name]=ERALIER(tableName[name])
             &&tableName[fy]=ERALIER(tableName[fy])
    )-1
    )
    VAR _balpre =
    MINX(_pre, tableName[Bal])
    VAR _exppre =
    MINX(_pre, tableName[Exp])
    RETURN
    _balpre + _exppre
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello There, 

      Thanks for your responds
      But it won’t give me 2023 values right I need to show

      namebalexfybal total
      a91020230
      a1520202419