Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@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.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |