Forum Discussion

Dev_Pat's avatar
Dev_Pat
Frequent Visitor
5 years ago
Solved

DAX for Year in Calculated Column

Hi Team,

I need a help to develop a DAX as calculated column .

Below is the detail requirement. Any help much appriciated.

 

I have data of last 4 year (2018-Jan to 2021-May) in rows shown below. Required a calculated column, which will give me 2019 volume number to respective month errecpective of any year in the row.

 

Below is a sample table.

 

 Volume2019 Volume
5/1/202161.95 
4/1/202179.90 
3/1/202177.82 
2/1/202131.26 
1/1/202173.70 
12/1/202023.29 
11/1/202031.01 
10/1/202032.62 
9/1/202096.02 
8/1/202057.03 
7/1/202092.29 
6/1/202044.06 
5/1/202069.18 
4/1/202036.51 
3/1/202040.27 
2/1/202048.76 
1/1/202020.02 
12/1/201957.93 
11/1/201979.38 
10/1/201919.48 
9/1/201919.13 
8/1/201920.03 
7/1/201935.74 
6/1/201955.23 
5/1/201997.64 
4/1/201935.52 
3/1/201968.00 
2/1/201937.86 
1/1/201958.07 
12/1/201895.56 
11/1/201864.16 
10/1/20184.02 
9/1/201876.18 
8/1/201851.35 
7/1/201834.06 
6/1/201894.95 
5/1/201869.14 
4/1/201845.19 
3/1/201880.90 
2/1/201817.84 
1/1/201847.14 
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    2019 Volume CC =
    VAR _currentrowmonth =
    MONTH ( Data[Date] )
    VAR _resulttable =
    SELECTCOLUMNS (
    FILTER (
    Data,
    YEAR ( Data[Date] ) = 2019
    && MONTH ( Data[Date] ) = _currentrowmonth
    ),
    "@result", Data[Volume]
    )
    RETURN
    _resulttable

     

     

    Link to the PBIX file 

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    2019 Volume CC =
    VAR _currentrowmonth =
    MONTH ( Data[Date] )
    VAR _resulttable =
    SELECTCOLUMNS (
    FILTER (
    Data,
    YEAR ( Data[Date] ) = 2019
    && MONTH ( Data[Date] ) = _currentrowmonth
    ),
    "@result", Data[Volume]
    )
    RETURN
    _resulttable

     

     

    Link to the PBIX file 

     

     

    • Dev_Pat's avatar
      Dev_Pat
      Frequent Visitor
      5 years ago

      Hi Kim,

       

      Thanks for quick revert.

      I'm getting an issue saying "A table of multiple values was supplied where a single value was expected".

      i think because I have two more column before date column.

      1. Market

      2. Category

       

      Could you help in modifying the current dax please?

       

      A great thanks for your help.

       

       

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    =sumx(filter(table,format(table[date],"yyyymm")=format(earlier(table[date]),"2019mm")),table[volumne])

    • Dev_Pat's avatar
      Dev_Pat
      Frequent Visitor
      5 years ago

      Hi Daniel,

      Thanks for quick revert.

      After using the dax I'm getting blank cell.

      i think because I have two more column before date column.

      1. Market

      2. Category

       

      Could you help in modifying the current dax please?

      Thanks