Forum Discussion
Helper IIPrevious Half Year - seasonally
Hello,
I have read couple of post about this topic, but coundn't find any good anwer.
I need to compare Half Years but seasonally, means 1HY 2024 vs 1HY 2023 and 2HY2024 vs 2HY2023.
Do you know any DAX measure that is working?
Thanks in advance!
lucie_raboch Are you using a standard calendar or a custom fiscal calendar? Also, do you want to compare partial half years to full half years or partial half years to equivalent partial half years? Here is one possible way to do it:
HY1 Standard = VAR __Year = MAX( 'Calendar'[Date] ) VAR __MaxDate = DATE( __Year, 6, 30 ) VAr __MinDate = DATE( __Year, 1, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( 'Calendar', [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( 'Table', [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result PHY1 Standard = VAR __Year = MAX( 'Calendar'[Date] ) - 1 VAR __MaxDate = DATE( __Year, 6, 30 ) VAr __MinDate = DATE( __Year, 1, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( ALL( 'Calendar' ), [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( ALL( 'Table' ), [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result HY1 to PHY1 = [HY1 Standard - [PHY1 Standard] HY2 Standard = VAR __Year = MAX( 'Calendar'[Date] ) VAR __MaxDate = DATE( __Year, 12, 31 ) VAR __MinDate = DATE( __Year, 7, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( 'Calendar', [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( 'Table', [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result PHY2 Standard = VAR __Year = MAX( 'Calendar'[Date] ) - 1 VAR __MaxDate = DATE( __Year, 12, 31 ) VAR __MinDate = DATE( __Year, 7, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( ALL( 'Calendar' ), [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( ALL( 'Table' ), [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result
2 Replies
- Greg_Deckler
Community Champion
lucie_raboch Are you using a standard calendar or a custom fiscal calendar? Also, do you want to compare partial half years to full half years or partial half years to equivalent partial half years? Here is one possible way to do it:
HY1 Standard = VAR __Year = MAX( 'Calendar'[Date] ) VAR __MaxDate = DATE( __Year, 6, 30 ) VAr __MinDate = DATE( __Year, 1, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( 'Calendar', [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( 'Table', [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result PHY1 Standard = VAR __Year = MAX( 'Calendar'[Date] ) - 1 VAR __MaxDate = DATE( __Year, 6, 30 ) VAr __MinDate = DATE( __Year, 1, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( ALL( 'Calendar' ), [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( ALL( 'Table' ), [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result HY1 to PHY1 = [HY1 Standard - [PHY1 Standard] HY2 Standard = VAR __Year = MAX( 'Calendar'[Date] ) VAR __MaxDate = DATE( __Year, 12, 31 ) VAR __MinDate = DATE( __Year, 7, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( 'Calendar', [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( 'Table', [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result PHY2 Standard = VAR __Year = MAX( 'Calendar'[Date] ) - 1 VAR __MaxDate = DATE( __Year, 12, 31 ) VAR __MinDate = DATE( __Year, 7, 1 ) VAR __Dates = SELECTCOLUMNS( FILTER( ALL( 'Calendar' ), [Date] <= __MaxDate && [Date] >= __MinDate ), "__Date", [Date] ) VAR __Table = SUMMARIZE( ALL( 'Table' ), [Date], "__Value", SUM( 'Table'[Value] ) ) VAR __Result = SUMX( FILTER( __Table, [Date] IN __Dates ), [__Value] ) RETURN __Result - DataNinja777
Super User
To compare Half Years (HY) seasonally (e.g., 1HY 2024 vs 1HY 2023 and 2HY 2024 vs 2HY 2023), you can create a DAX measure that dynamically calculates the values for the corresponding half-year in the previous year. Here’s how to achieve this:
- Add a "Half Year" Column to the Date Table
Create a calculated column in your Date Table to identify whether each date falls in the first half (1HY) or second half (2HY) of the year:
HalfYear = IF( MONTH('DateTable'[Date]) <= 6, "1HY", "2HY" )This will label all dates as either "1HY" or "2HY."
- Create a DAX Measure to Calculate Seasonal Comparison
Here’s a measure that compares the selected half-year (e.g., 1HY 2024) to the same half-year in the previous year (e.g., 1HY 2023):
Seasonal Comparison = VAR CurrentYear = SELECTEDVALUE('DateTable'[Year]) VAR CurrentHalfYear = SELECTEDVALUE('DateTable'[HalfYear]) -- Calculate the current year's value VAR CurrentValue = CALCULATE( SUM('FactTable'[Value]), -- Replace with your column to aggregate 'DateTable'[Year] = CurrentYear, 'DateTable'[HalfYear] = CurrentHalfYear ) -- Calculate the previous year's value VAR PreviousValue = CALCULATE( SUM('FactTable'[Value]), 'DateTable'[Year] = CurrentYear - 1, 'DateTable'[HalfYear] = CurrentHalfYear ) -- Return the difference or percentage change RETURN IF( ISBLANK(CurrentValue) || ISBLANK(PreviousValue), BLANK(), CurrentValue - PreviousValue )- Add Percentage Change (Optional)
If you’d like to include percentage change, extend the measure:
Seasonal Comparison % = VAR CurrentYear = SELECTEDVALUE('DateTable'[Year]) VAR CurrentHalfYear = SELECTEDVALUE('DateTable'[HalfYear]) -- Current and Previous Values VAR CurrentValue = CALCULATE( SUM('FactTable'[Value]), 'DateTable'[Year] = CurrentYear, 'DateTable'[HalfYear] = CurrentHalfYear ) VAR PreviousValue = CALCULATE( SUM('FactTable'[Value]), 'DateTable'[Year] = CurrentYear - 1, 'DateTable'[HalfYear] = CurrentHalfYear ) -- Calculate Percentage Change RETURN IF( ISBLANK(CurrentValue) || ISBLANK(PreviousValue), BLANK(), DIVIDE(CurrentValue - PreviousValue, PreviousValue, 0) )Best regards,
