cancel
Showing results for
Did you mean:
Frequent Visitor

## Help, Please, with DAX Date Processing

I’m honing my DAX skills in Power BI Desktop, and I’ve run into a snag that I hope the community can help me with.

I’ve been using Reza Rad’s excellent book, Power BI from Rookie to Rock Star: Book04 – “Power BI Modeling and DAX”.  In this exercise, we use the MS Adventure Works DB to determine the Total Sales to each customer and, using a slicer to select the most-recent “x” number of days, we then calculate the total sales dollars for each of those customers for that selected period.

I had no problem with getting the Total Sales per customer but, trying to get the Sales per Customer for the selected time period, PBI runs for 20+ minutes, then gives an error of “not enough memory” on my 8 GB PC, with essentially nothing else running.  As these tables are used for training, they are fairly small, so I'm sure I'm doing something wrong.

In the book the code to select the Sales for the selected time period is as follows:

Last Period Revenue = CALCULATE(

SUM(FactInternetSales[SalesAmount]),

DATESBETWEEN(

DimDate[FullDateAlternateKey],

LASTDATE(DimDate[FullDateAlternateKey])

)

)

After the first failure, I copied the essential columns of the Adventure Works FactInternetSales table to a new table, using “SELECTCOLUMNS” but this didn’t work either.  (I do still have the original table in PBI, but I believe that shouldn’t impact the extract, as we’re now only working with the subset.)

I then tried simplifying the code, creating the following measures (Selected Period is the slicer's number of days for creating the range):

First Date In Range = (CALCULATE([Last Date In Range] -[Selected Period]))

Last Date In Range = LASTDATE('Subset of FactInternetSales21'[OrderDate])

With Selected Period = 60, I used cards to verify these two values: 6/1/2008 and 7/31/2008 are correct.

I tried another approach for the results columns, calculating it as:

Last Period Revenue = CALCULATE(SUM('Subset of FactInternetSales21'[SalesAmount]), DATESBETWEEN(DimDate5[FullDateAlternateKey], [First Date In Range], [Last Date In Range]))

This still locks up PBI.

Any suggestions would be most appreciated!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Help, Please, with DAX Date Processing

Hi @Mike56 ,

I'm glad to cooperate you to solve the problem. If the answer is helpful, can you please accept it as a solution? Then we are able to close the thread. And more people who have the same request will benefit here.

Best Regards,

Xue Ding

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

10 REPLIES 10
Community Support Team

## Re: Help, Please, with DAX Date Processing

Hi @Mike56 ，

For the problem of data loading slowly, you could reference the blog to analyze your report and improve the performance.

For incorrect results, I can't get you just based on description.  Please share a sample pbix file and your expected results. Then we will understand clearly.

Best Regards,

Xue Ding

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

Frequent Visitor

## Re: Help, Please, with DAX Date Processing

Thanks, Xue Ding, for trying to help!

I'm not yet to the level of trying to fine-tune performance, but instead I think I'm probably doing something wrong in my DAX formulas.  I'm not sure that posting this response will allow me to upload the PBIX, but I'll try.  The expected results are the sum of total sales dollars for each customer for the selected period (which in my test is the latest 60 days of Order Dates).

Thanks again!

Frequent Visitor

## Re: Help, Please, with DAX Date Processing

I hoped to get a link for uploading the PBIX when I clicked "Post", but obviously didn't . . .  Could you instruct me how to upload it, please?

Thanks again for your help to this newbie!!

Community Support Team

## Re: Help, Please, with DAX Date Processing

Hi @Mike56 ,

Sorry for late back. You can share it via DropBox, One Drive, Drive or another similar tool.

Best Regards,

Xue Ding

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

Frequent Visitor

## Re: Help, Please, with DAX Date Processing

My company doesn't allow the use of DropBox, etc., so Xue Ding was kind enough to allow me to reach out to him offline.  Following is the email to him, to keep the trail intact.

--------------------

Attached is the TEST-3 PBIX.  As a long-time programmer/analyst, though not in PBI, I made a couple of changes in trying to debug this since my original post.  For input, I used the DimCustomer, DimDate5 and FactInternetSales21 sheets in the Microsoft AdventureWorksDW2012.xlsx test file.

When I hard-code the dates (in the “Test Date Range” measure), we get the correct results, a portion of which is shown below –

 CustomerKey FullName Total Revenue Test Date Range 28866 Aaron Adams 117.96 20285 Aaron Alexander 69.99 69.99 20075 Aaron Allen 3,399.99 17862 Aaron Baker 1,750.98 12067 Aaron Bryant 133.96 21414 Aaron Butler 14.98 14.98 21151 Aaron Campbell 1,155.48 27916 Aaron Carter 39.98 39.98 28187 Aaron Chen 39.98 16749 Aaron Coleman 61.96 61.96 27663 Aaron Collins 6,047.32 18695 Aaron Diaz 6,029.57 19692 Aaron Edwards 94.48 25415 Aaron Evans 2,433.04 14617 Aaron Flores 1,538.56 15566 Aaron Foster 4,912.47 2,482.03 18804 Aaron Gonzales 1,810.46 1,810.46 20123 Aaron Gonzalez 132.97

But, when I use the calculated range (in the “Last Period Revenue” measure), it seems to loop until all memory is gone and then it throws the error.

For your convenience, the measures are shown as follows:

Test Date Range = CALCULATE(SUM('Subset of FactInternetSales21'[SalesAmount]), DATESBETWEEN(DimDate5[FullDateAlternateKey], DATE(2008,06,01), DATE(2008,07,31)))

Last Period Revenue = CALCULATE(SUM('Subset of FactInternetSales21'[SalesAmount]), DATESBETWEEN(DimDate5[FullDateAlternateKey], [First Date In Range], [Last Date In Range]))

Both “First Date In Range” and “Last Date In Range” appear to be correct, as they are correctly shown in the Card visuals.

I will post the contents of this email, so others may gain from your assistance.

Community Support Team

## Re: Help, Please, with DAX Date Processing

Hi @Mike56 ,

I found that the "Subset of FactInternetSales21" table is from "FactInternetSales21" table. And there is about 6w rows in it. The two tables take up lots of memory. And the calculation about "Subset" table causes the report slowly. If you don't need "FactInternetSales21" table, I think you could remove it and create the  "Subset of FactInternetSales21" table in Query Editor directly. If you still need it, you could remove some columns.Otherwise, you could try to connect the data source with DirectQuery mode.

The ways can solve the problem about memory. However the report still runs slowly, the measure takes too long time (about several hours) to respond so that I can't get a result. You could reference the blogs that my first answer mentioned to optimize it.

Best Regards,

Xue Ding

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

Frequent Visitor

## Re: Help, Please, with DAX Date Processing

Thank you so much for all your help, Xue Ding!!

Community Support Team

## Re: Help, Please, with DAX Date Processing

Hi @Mike56 ,

I'm glad to cooperate you to solve the problem. If the answer is helpful, can you please accept it as a solution? Then we are able to close the thread. And more people who have the same request will benefit here.

Best Regards,

Xue Ding

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

Frequent Visitor

## Re: Help, Please, with DAX Date Processing

My sincere apologies for the delay, Xue Ding!!

After drastically cutting the "Subset of FactInternetSales21" file, to the 1388 records with the lowest CustomerKeys, it now completes and, while I still have some issues to work on, I think those are ones I can handle.  My main error was in grossly underestimating PBI's use of memory.

Again, my appreciation for your help!!

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,096)