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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ricardo_cvo
Helper I
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
Washivale
Resolver V
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

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

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
https://www.linkedin.com/in/excelenthusiasts/

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

Hello,

 

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

 

Regards

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

gregcd
Frequent Visitor

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

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

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

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

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?

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

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

 

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
https://www.linkedin.com/in/excelenthusiasts/

hanks for the answer

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?

Hi,

 

I will need to see your PBI file.  Share the link from where i can download your file.  Show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

 

Thanks for the support

Follow the link from the pbix file:

 

Donwload PBIX

 

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

Hi,

 

When i click on the Downloaod button on that site, another window opens up showing some advertising.  Please upload the file to some site like Google Drive/OneDrive and then share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

 

Thanks for the support, follow the link published:

 

Archive OnDrive

 

 

Regards

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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