Forum Discussion

Anya_T's avatar
Anya_T
Frequent Visitor
4 years ago
Solved

Sum of values

Hi,

 

There is data in

YearMonth Values
2020Jan0
2020Feb0
2020Mar0
2020Apr0
2020May0
2020Jun0
2020Jul39785
2020Aug25375
2020Sep384134
2020Oct193531
2020Nov2293
2020Dec16063
2021Jan0
2021Feb0
2021Mar0
2021Apr0
2021May0
2021Jun0
2021Jul64451.7
2021Aug41107.5
2021Sep622297.1
2021Oct313520.2
2021Nov3714.66
2021Dec26022.06

 

I want to create column or Measure with H1'20 which will add values of year 2020 from jan to jun and , H2'20 from jul to dec and similarly for 2021.

Then i want to compare H1'20 with H1'21. Please suggest best way to do it.

 

I am creating a measure but it is adding sum of whole column rather then first 6 and then next 6 as per year.

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi Anya_T ,

     

    Create a column with below code:-

    H's = 
var month_num = Month(DATEVALUE('Table (6)'[Year]&"/"&'Table (6)'[Month ]&"/"&"1"))
return if(month_num <= 6,"H1","H2")

     

    Now create a measure like this:-

    Comparision =
VAR H1_2020 =
    CALCULATE (
        SUM ( 'Table (6)'[Values] ),
        'Table (6)'[Year] = 2020,
        'Table (6)'[H's] = "H1"
    )
VAR H1_2021 =
    CALCULATE (
        SUM ( 'Table (6)'[Values] ),
        'Table (6)'[Year] = 2021,
        'Table (6)'[H's] = "H1"
    )
RETURN
    H1_2021 - H1_2020

     

    Thanks,

    Samarth

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anya_T ,

     

    Create a column with below code:-

    H's = 
var month_num = Month(DATEVALUE('Table (6)'[Year]&"/"&'Table (6)'[Month ]&"/"&"1"))
return if(month_num <= 6,"H1","H2")

     

    Now create a measure like this:-

    Comparision =
VAR H1_2020 =
    CALCULATE (
        SUM ( 'Table (6)'[Values] ),
        'Table (6)'[Year] = 2020,
        'Table (6)'[H's] = "H1"
    )
VAR H1_2021 =
    CALCULATE (
        SUM ( 'Table (6)'[Values] ),
        'Table (6)'[Year] = 2021,
        'Table (6)'[H's] = "H1"
    )
RETURN
    H1_2021 - H1_2020

     

    Thanks,

    Samarth

     

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

    Anya_T , Create a date table. If you do not have date in your table, create with help from month year.

     

    Add these column in date table

    Start Year = STARTOFYEAR('Date'[Date],"12/31")
    Half = Half = if(datediff([start year],[Date],month)<6,1,2)
    Half Year = [Start Year]*100 + [Half]
    Half Year Rank = RANKX(all('Date'),'Date'[Half Year Start],,ASC,Dense)

     

     

    Try measure like these
    This Half Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])))
    Last Half Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-1))
    3rd Last Half Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Half Year Rank]=max('Date'[Half Year Rank])-3))

     

     

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.