cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Half year YTD

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.

10 REPLIES 10
Super User

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]

Super User

Easy enough,

For fun only, a showcase of powerful Excel formulas,

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

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

Super User

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

Helper V

thanks @parry2k , i wil check and get back to u

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

@ThxAlot @Ahmedx @Ashish_Mathur @gmsamborn , thanks guys.. wil get back after implementing one of the solutions posted

Super User

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

Helper V

@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

Super User

Would a column in the date table make things easier?

Semester = IF( [Quarter] <= 2, 1, 2 )