Forum Discussion

Anya_T's avatar
Anya_T
Frequent Visitor
4 years ago
Solved

Sum of values

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

     

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.