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
minseon
Frequent Visitor

Assistance Needed with Aggregating Sales from Past Years Based on Date Slicer

Hi

 

i wonder how to aggregate something from few years ago until now

 

i have two tables. one is calendar for slicer the other one is transaction table.

i set date slicer year, month.

 

i want to sum sales from 2 years ago 1st of month to end of this month.

for example, if i seleted slicer as 2022 , 01 then, sum sales between 2020-01-01 and 2020-01-31.

 

i tried several times but didn't solved.

 

thanks for your help 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @minseon ,

To sum sales from 2 years ago 1st of month to end of this month, you can use the following DAX formula:

Sales 2 Years Ago = 
CALCULATE(
    SUM('Transaction Table'[Sales Amount]),
    DATESBETWEEN(
        'Calendar'[Date],
        DATE(YEAR(SELECTEDVALUE('Calendar'[Date]))-2, MONTH(SELECTEDVALUE('Calendar'[Date])), 1),
        LASTDATE(DATE(YEAR(SELECTEDVALUE('Calendar'[Date]))-2, MONTH(SELECTEDVALUE('Calendar'[Date])), 1))
)

This formula calculates the sum of sales amount from the transaction table between the first day of the month two years ago and the last day of the month two years ago based on the date slicer selection.

 

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

 

 

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @minseon ,

To sum sales from 2 years ago 1st of month to end of this month, you can use the following DAX formula:

Sales 2 Years Ago = 
CALCULATE(
    SUM('Transaction Table'[Sales Amount]),
    DATESBETWEEN(
        'Calendar'[Date],
        DATE(YEAR(SELECTEDVALUE('Calendar'[Date]))-2, MONTH(SELECTEDVALUE('Calendar'[Date])), 1),
        LASTDATE(DATE(YEAR(SELECTEDVALUE('Calendar'[Date]))-2, MONTH(SELECTEDVALUE('Calendar'[Date])), 1))
)

This formula calculates the sum of sales amount from the transaction table between the first day of the month two years ago and the last day of the month two years ago based on the date slicer selection.

 

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

 

 

 

 

123abc
Community Champion
Community Champion

I can provide you with a general idea that you can adapt to your situation.

Assuming you have a date column in your transaction table and you're using a tool like Microsoft Excel, Power BI, or a similar tool, here's a general approach using DAX (Data Analysis Expressions) language:

Let's assume your transaction table is named Sales and has a column Date representing the transaction date.

  1. Create a Date Table: Make sure you have a separate calendar table with a continuous range of dates that covers the entire period of your sales data.

  2. Create a Relationship: Ensure there's a relationship between the Date column in the Sales table and the corresponding date column in your calendar table.

  3. Create a Measure: Create a new measure that calculates the sum of sales for the selected range. In DAX, it might look something like this:

SalesSum = CALCULATE(SUM(Sales[SalesAmount]),
FILTER(ALL('Calendar'),
'Calendar'[Date] >= MIN('Calendar'[Date]) &&
'Calendar'[Date] <= MAX('Calendar'[Date]) &&
'Calendar'[Year] = YEAR(TODAY()) - 2 &&
'Calendar'[Month] = SELECTEDVALUE('Calendar'[Month])
)
)

 

  1. In this example, YEAR(TODAY()) - 2 calculates the year two years ago, and SELECTEDVALUE('Calendar'[Month]) gets the selected month from the slicer.

  2. Use the Measure in Your Report: Drag the SalesSum measure into your report, and it should dynamically aggregate the sales based on the selected slicer values.

Remember to adjust the table and column names based on your actual data model.

If you provide more details about the specific tool you're using, I can give you a more tailored solution.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

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