Forum Discussion
Time intelligence Past year sales
You can create a DAX query to return the timeframes. In this example I've started the fiscal year on February and the WTD on Saturday.
You can filter or remove the timeframes you don't need.
Calendar Timeframe =
VAR _today_date = TODAY()
VAR _yesterday_date = _today_date - 1
VAR _week_start = _today_date - WEEKDAY ( _today_date, 1 ) // starts on Saturday
VAR _week_end = _today_date - WEEKDAY ( _today_date, 1 ) + 6
VAR _month_start = DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_end = EOMONTH( _today_date, 0)
VAR _quarter_start = DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 )
VAR _quarter_end = EOMONTH(EDATE(_quarter_start, 2), 0)
VAR _fiscal_year = YEAR(EDATE( _today_date, 2))
VAR _fiscal_year_start = DATE( _fiscal_year - 1, 02, 01)
VAR _fiscal_year_end = DATE( _fiscal_year, 01, 31)
VAR _tomorrow_date = IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1)
VAR _calendar_year = YEAR(_today_date)
VAR _calendar_year_start = DATE( _calendar_year , 01, 01)
VAR _calendar_year_end = DATE( _calendar_year, 12, 31)
VAR _previous_month_start = IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1))
VAR _previous_month_end = DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start,0)))
VAR _previous_quarter_start = EDATE(_quarter_start, -3)
VAR _previous_quarter_end = EOMONTH(EDATE(_quarter_start, -1), 0)
VAR _previous_fiscal_year_start = DATE( _fiscal_year - 2, 02, 01)
VAR _previous_fiscal_year_end = DATE( _fiscal_year - 1, 01, 31)
VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01)
VAR _previous_calendar_year_end = DATE( _calendar_year - 1, 12, 31)
VAR _result =
UNION (
ADDCOLUMNS (CALENDAR ( _today_date, _today_date), "Timeframe", "Today", "Timeframe Order", 1 )
, ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date), "Timeframe", "Yesterday", "Timeframe Order", 2 )
, ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ), "Timeframe", "Previous Week", "Timeframe Order", 3 )
, ADDCOLUMNS (CALENDAR ( _week_start, _week_end ), "Timeframe", "Current Week", "Timeframe Order", 4 )
, ADDCOLUMNS (CALENDAR ( _week_start, _today_date ), "Timeframe", "WTD", "Timeframe Order", 5 )
, ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ), "Timeframe", "Previous Month", "Timeframe Order", 6 )
, ADDCOLUMNS (CALENDAR ( _month_start, _month_end ), "Timeframe", "Current Month", "Timeframe Order", 7 )
, ADDCOLUMNS (CALENDAR ( _month_start, _today_date ), "Timeframe", "MTD", "Timeframe Order", 8 )
, ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ), "Timeframe", "Previous Qtr", "Timeframe Order", 9 )
, ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ), "Timeframe", "Current Qtr", "Timeframe Order", 10 )
, ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ), "Timeframe", "QTD", "Timeframe Order", 11 )
, ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ), "Timeframe", "Previous Year", "Timeframe Order", 12 )
, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ), "Timeframe", "Current Year", "Timeframe Order", 13 )
, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ), "Timeframe", "YTD", "Timeframe Order", 14 )
, ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ), "Timeframe", "Rest of Year", "Timeframe Order", 15 )
, ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ), "Timeframe", "Previous Calendar Year", "Timeframe Order", 16 )
, ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ), "Timeframe", "Current Calendar Year", "Timeframe Order", 17 )
, ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ), "Timeframe", "Calendar YTD", "Timeframe Order", 18 )
, ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ), "Timeframe", "Rest of Calendar Year", "Timeframe Order", 19 )
)
RETURN
_result
aduguid @Thanks for your reply. My requirement requires that when filter by date it should provide the result of the fiscal week of the same period last year. For example, this year the week period is 4th of February 2024 and ends on the 10th of February 2024. And the last year week period is 5th February 2023 and ends on the 11th of February 2023. If I should select on my date slicer, for the current year I should see the sales of 4th February 2024 to the 10th of February 2024 and I should see the sales of of 5th February 2023 to 11th February 2023 as my last year sales.
- aduguid2 years ago
Memorable Member
I'll give it another go