Time intelligence Past year sales

[ANE2]

Hi, I am trying to calculate last year sales using a 454 fiscal calendar. The normal time intelligence functions like dateadd, same period last year don't seem to work. The fiscal week on my calendar starts on Saturday, the fiscal year starts in February. I am trying to calculate the last year sales. I have a date table already. Can anyone help? Thanks

[v-kaiyue-msft]

Hi @ANE2 ,

1. Create a date table that has no relationship to the original table.

DateTable =
ADDCOLUMNS (
CALENDAR(DATE(2023,1,1), DATE(2024,12,31)),
// CALENDARAUTO(),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),
"Quarter No", CEILING(MONTH([Date])/3, 1),
"Month No", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Month Short Name", FORMAT([Date], "MMM"),
"Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),
"DateSort", FORMAT([Date], "yyyyMMdd"),
"Day Name", FORMAT([Date], "dddd"),
"Details", FORMAT([Date], "dd-MMM-yyyy"),
"Day Number", DAY ( [Date] ),
"Week Number",WEEKNUM([Date],1)
)

2. Create the Week Number column for the Sales table.

Column =
WEEKNUM('sales'[date],1)

3. create measure to get the sales sum for the same week last year.

Measure 3 =
CALCULATE(SUM('sales'[sales]),FILTER(ALL('sales'),'sales'[Column] = SELECTEDVALUE('DateTable'[Week Number]) && YEAR('sales'[date]) = YEAR( MAX('sales'[date])) - 1))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANE2]

@v-kaiyue-msft sorry this reply was meant for you. @Thanks this works the only issue now is if apply filters on a slicer such as product category and product sub category, the filters on the slicers do not work is there a way to fix this?

[v-kaiyue-msft]

Hi @ANE2 ,

Sorry, I don't quite understand your problem, could you use a pbix file, screenshot or other way to describe your problem in more detail and visually so that I can better help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANE2]

@v-kaiyue-msft what I mean is when I select a filter from the slicer the values are static and also I am having a straight line on the line chart for the previous year value which is not what I want. I want the previous year values on the line chat to be dynamic just like this year values 

 

[v-kaiyue-msft]

Hi @ANE2 ,

Can I ask you if your LY Net Demand is obtained by calculating the columns? If yes, please change it to be calculated using MEASUREMENT. Calculated columns are static values that are added to a table and are calculated row by row as the data is loaded. In contrast, a measure is a dynamic value that changes based on what you are viewing in the report, calculated dynamically in the context of the visual object's filters. More detailed information can be found at the link: Understanding Measures vs Calculated Columns in Power BI (gorilla.bi)

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANE2]

@v-kaiyue-msft @Hi, no LY Net Demand is calculated using a measure not a calculated column 

[v-kaiyue-msft]

Hi @ANE2 ,

If you are basing your LY Net Demand on my previous reply, then it gets the sum of the same week as last year, so it doesn't change based on each day in the line graph.

Also, if you want it to vary with the number of days, then consider the dates relative to last year, not the number of weeks, because as mentioned before, the same number of weeks does not correspond to the same number of days, which contradicts the display you are looking for.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANE2]

@aduguid @Thanks this works the only issue now is if apply filters on a slicer such as product category and product sub category, the filters on the slicers do not work is there a way to fix this?

[v-kaiyue-msft]

Hi @ANE2 ,

Thanks to @aduguid  for their replies, allow me to provide another insight:

The dateadd function returns a table that contains a column of dates that move into the future or into the past at specified intervals from the date in the current context. It must be a date that already exists in the table; if you don't have a date from a year ago in your table, it will return null.

You can see this example. I have these dates in my table.

To get the date three years from now, it returns null.

To get the date three days from now, it would return data that already exists in the table.

To get the date one year ago, you can use an expression similar to the following.

Measure =
DATE(YEAR(MAX('Table'[date]))-1,MONTH(MAX('Table'[date])),DAY(MAX('Table'[date])))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ANE2]

Hi v-kaiyue-msft, thank you for your responses. The issue I am facing is that for example this year my current week starts on the 4th and ends on the 10th while last year this same period started on the 5th and 11th. When I filter my current week period from 4th to 10th, I don't get the right result of 5th to 11th last year as well. This is what I am trying to solve

[aduguid]

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

[ANE2]

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

[aduguid]

I'll give it another go