Sum of values

[Anya_T]

Hi,

There is data in

Year	Month	Values
2020	Jan	0
2020	Feb	0
2020	Mar	0
2020	Apr	0
2020	May	0
2020	Jun	0
2020	Jul	39785
2020	Aug	25375
2020	Sep	384134
2020	Oct	193531
2020	Nov	2293
2020	Dec	16063
2021	Jan	0
2021	Feb	0
2021	Mar	0
2021	Apr	0
2021	May	0
2021	Jun	0
2021	Jul	64451.7
2021	Aug	41107.5
2021	Sep	622297.1
2021	Oct	313520.2
2021	Nov	3714.66
2021	Dec	26022.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.

[Samarth_18 · Accepted solution]

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]

@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.