Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ANE2
Frequent Visitor

Time intelligence Past year sales

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

13 REPLIES 13
v-kaiyue-msft
Community Support
Community Support

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))

 

vkaiyuemsft_0-1715161728007.png

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.

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

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.

@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 

IMG_0684.jpeg

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.

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

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.

@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
Community Support
Community Support

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.

vkaiyuemsft_0-1715133893424.png

 

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

vkaiyuemsft_1-1715133903183.png

 

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

vkaiyuemsft_2-1715133915655.png


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])))

vkaiyuemsft_3-1715133946244.png

 

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.

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
Solution Sage
Solution Sage

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.

aduguid_0-1715124663030.png

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_1-1715124778379.png

ANE2
Frequent Visitor

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

I'll give it another go

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors