Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
i want to calculate Half year YTD measure for a data set. Can anyone please help me out with the expression to calculate the same.
It's better for you to create a calculated column in the calendar table like this
Half = IF('Calendar'[Month number]<=6, "Half-1",Half-2)
and then you just need to create a matrix where you have 'Calendar'[Half] on the columns and years on the rows, and then simply measure = SUM(table[Sales]
@ThxAlot @this is awesome. Well done.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@ak77 try this measure, doesn't matter how you visualize the data, it will always do the running total of 1st half and then 2nd half, cheers:
RT Sales Half Yearly =
VAR __SelectedDate = MAX ( 'Calendar'[Date] )
VAR __MonthOfSelectedDate = MONTH ( __SelectedDate )
VAR __YearOfSelectedDate = YEAR ( __SelectedDate )
VAR __StartDate = IF (__MonthOfSelectedDate < 7, DATE ( __YearOfSelectedDate, 1, 1 ), DATE ( __YearOfSelectedDate, 7, 1 ) )
VAR __EndDate = IF ( __MonthOfSelectedDate < 7, DATE ( __YearOfSelectedDate, 6, 30 ), DATE ( __YearOfSelectedDate, 12, 31 ) )
RETURN
CALCULATE (
[Sales],
FILTER (
CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] >= __StartDate, 'Calendar'[Date] <= __EndDate ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
and here is good example of how it will work, you have sale by month and running total will reset 1st half and 2nd half:
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
If you select June in the month slicer and 2023 in the year slicer, then this measure will add the figures in the sales column from Jan - Jun
Measure = calculate(sum(Data[sales]),datesytd(calendar[date],"31/12"))
Hope this helps.
@ThxAlot @Ahmedx @Ashish_Mathur @gmsamborn , thanks guys.. wil get back after implementing one of the solutions posted
@ak77 I guess you want 1st half and 2nd half - two measures, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k , Thanks for reply. i have a date and on selection of it For the measure should check if month number >6 then calculate sum for first 6 months , else last 6 months
Ex: 28 Nov 2023, month is 11 so calculate last 6 months sum from july till date
Please let me know if this is possible
Would a column in the date table make things easier?
Semester = IF( [Quarter] <= 2, 1, 2 )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |