cancel
Showing results for
Did you mean:
Helper I

## Dax Sales by Period

Good afternoon everyone,

I am new to dax and need to get the sum of sales as follows:
Sales from day 1 to day 30
Sales from the 31st to the 60th
It's possible?

Thank you

21 REPLIES 21
Resolver V

HI Ricardo,

Check the following if it works:

Sales 1 to 30 Days = CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],MAX(fSales[Date of Sale])-60,MAX(fSales[Date of Sale])-30))

Sales 31 to 60 Days = CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],MAX(fSales[Date of Sale])-30,MAX(fSales[Date of Sale])))

Thanks,

Sandeep

Helper I

Thanks for the feedback,

I tested the Sales 31 to 60 Days formula that in the example is from 06/13 to 07/07 and is giving 123537,10 how much the value should be 118692.82.

Regards

Super User

Hi,

Looks like a -1 or +1 is missing somewhere.  Try 61 instead of 60 or 31 instead of 30.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver V

Hi Ricardo,

Please give it a try with below measures.

Sales 31 to 60 Days =

var ld = CALCULATE ( MAX ( dCalendar[Date] ), ALLSELECTED ( dCalendar[Date] ))
var sd = ld-30
return
CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],sd,ld))

Sales 1 to 30 Days =
var ld = CALCULATE ( MAX ( dCalendar[Date] ), ALLSELECTED ( dCalendar[Date] ))
var sd = ld-60
var lp = sd + 30
return
CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],sd,lp))

let me know if it works

Helper I

Hello,

Thanks for returning, I tested the formulas sent, but the values disappear when the formula is applied.

Regards

Resolver V

Hi Ricardo,

When you say  01 to 30 days out of 60, should we be looking at 60 days prior from today's date , or 60 days prior to the last date on your table.

Also, Do you have a date slicer on your report, last formula i sent will work on the dynamic date selections (if you could add a date slicer and select date from which backdated calculation should happen, it would return values.)

Thanks

Frequent Visitor

Can you attach a screenshot of the data you are working with

Microsoft

Hi @ricardo_cvo,

It's hard to provide specific solution without sample table. After research, the following thread and articles are useful. In my oppion, you can do your cases after learn them carefully.

filtering-and-comparing-different-time-periods-with-power-bi
Dynamically comparing current totals to last year totals
Total Sales X Custom Period (day 26 current month to day 25 next month)
POWER BI: Modeling

Thanks,
Angelia

Helper I

Thank you all for the answers:

The data unfortunately can not share, but I am using the formulas below for 30 days and for 60 days:

30 Days = CALCULATE(SUM(FatSales[);FILTER(ALL(FatVenda[Date of Sale]);FatVenda[Date of Sale]>TODAY()- 30))

60 Days = CALCULATE(SUM(FatSales[Date of Sale]);FILTER(ALL(FatVenda[Date of Sale]);FatVenda[Date of Sale]>TODAY()- 61))

Tanks

Microsoft

Hi @ricardo_cvo,

The syntax is right, you get the expected result? If it is, please mark your reply as answer, so more people will benefit from here.

Thanks,
Angelia

Helper I

Hello,

Thank you for replying, yes the synthesis is correct, but it brings the data of the last 30 days and the last 31 days, I need to get it as follows:

Adding Sales from Day 01 to Day 30
Adding Sales from Day 31 to Day 60

Can you understand?

Microsoft

Hi @ricardo_cvo,

If today is 2018/8/7, what's the period? Could you give an example? I test using your formula, which works fine.

Thanks,
Angelia

Helper I

Good afternoon,

I have the formula below that is already correct taking the last 30 days

Last 30 Days = CALCULATE (SUM (Table [Total]); DATESINPERIOD (Table [Date]; MAX (Table [Date]);

This formula returns me the sum of the sales of the last 30 days, for example, the sales of the day 9/08 retroactive until the day 11/07.

The formula that I need is to pick up the sales from day 31, day 32, day 33 and so on until the 60 day that in my case is 10/06.

Did you get it?

Tanks

Super User

Hi,

Try this

=CALCULATE (SUM(Table[Total]); DATESBETWEEN(Calendar[Date],MIN(CALENDAR[Date])-60,MIN(CALENDAR[Date])-30))

There should be a Calendar Table and a relatioship from the Date column of your Table to the Date column of your Calendar Table.  In the Calendar Table, extract the month from the Date column using this calculated column formula

=FORMAT(Calendar[Date])

In the filter section, drag the Month column and select a certain month.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I did the formula however the values were left blank, it follows to formulate that I am using

Last 60 Days = CALCULATE (SUM (FatSum [Suml]); DATESBETWEEN (DimCalendar [Data]; MIN (DimCalendar [Data]) - 60; MIN (DimCalendar [

My Month field is in date, what is the purpose of the format function?

What can it be?

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hello,

Thanks for the support

The value for the Last 60 Days field should be somewhere around 120,866.92 and is giving 308,388.11 taking into account the 31st day that is 7/07/2018 until the 60th day that is 6/08/2018.

The 30-day calculation is picking up correctly

Regards

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hello,

Archive OnDrive

Regards

Microsoft

Hi @ricardo_cvo,

Yes, you can use the DATESINPERIOD function to calculate the sum sales in a period. You can change the interval parameter, the following first formula calculates the total sum from last 31 days until the last date of your date, the second one calculates the total sum from last 60 days until the last date of your date.

```Last 31 Days =
CALCULATE (
SUM ( Table[Total] ),
DATESINPERIOD ( Table[Date], LASTDATE ( Table[Date] ), -31, DAY )
)

Last 60 Days =
CALCULATE (
SUM ( Table[Total] ),
DATESINPERIOD ( Table[Date], LASTDATE ( Table[Date] ), -60, DAY )
)
```

Best Regards,
Angelia

Announcements

#### Launching new user group features

Learn how to create your own user groups today!