Half year YTD

[ak77]

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.

[Ahmedx]

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]

Easy enough,

For fun only, a showcase of powerful Excel formulas,

[parry2k]

@ThxAlot @this is awesome. Well done.

[parry2k]

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

[ak77]

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

[Ashish_Mathur]

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.

[ak77]

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

[parry2k]

@ak77 I guess you want 1st half and 2nd half - two measures, correct?

[ak77]

@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

[gmsamborn]

Would a column in the date table make things easier?

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