Forum Discussion
Help, Please, with DAX Date Processing
Hi Anonymous ,
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.
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!
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!!
Hi Anonymous ,
Sorry for late back. You can share it via One Drive for business.
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.
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.
Hi Anonymous ,
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.
Hope the answer will help you a little. I have tried my best.
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.