Forum Discussion

APM's avatar
APM
Helper I
3 years ago
Solved

Next 4 weeks last year

HI,

I would like to calculate the next 4 weeks from last year. I have two tables:

 

Date table:

Date
2/11/2022
2/12/2022
2/13/2022

etc...

 

and 

Sales table:

DateRevenue
2/11/2022 $4
2/12/2022$5
2/13/2022$6

...etc.

 

I want to know what total sales were during the next 4 weeks from today LAST YEAR.

 

so basically: what were sales 2/10/2022 to 3/10/2022? But this needs to be rolling, not something where every day I change the formula with a new date range.

 

I've googled everything and nothing works. Help would be amazing. 

 

Need Help
Tutorial Requests
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    Hello APM,

     

    To calculate the total sales for the next 4 weeks from last year, you can use the following DAX formula:

     

    Total Sales Next 4 Weeks Last Year =
CALCULATE(
SUM(Sales[Revenue]),
DATESYTD(
EOMONTH(TODAY() - 365, -3),
TODAY() - 365
),
USERELATIONSHIP(Date[Date], Sales[Date])
)

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello APM,

     

    To calculate the total sales for the next 4 weeks from last year, you can use the following DAX formula:

     

    Total Sales Next 4 Weeks Last Year =
CALCULATE(
SUM(Sales[Revenue]),
DATESYTD(
EOMONTH(TODAY() - 365, -3),
TODAY() - 365
),
USERELATIONSHIP(Date[Date], Sales[Date])
)
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    This formula uses the DATESYTD function to retrieve the sum of the revenue from the Sales table within the specified date range. The range starts from the end of the month 3 months ago and ends at the current date minus 365 days (to get last year's date). The USERELATIONSHIP function is used to establish a relationship between the two tables based on the Date column.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    This formula will be rolling and will calculate the total sales for the next 4 weeks from the last year every time you refresh your report. 

     

    Let me know if you need further help.