Measure Taking Lot of Time to Load

[Nimai123]

I had created a measure to calculate This Year sales and I was applying the below Measure 

This Year Test = SUMX(Main,TOTALYTD(SUMX(Main,SUM([Sales])),'Invoice Date'[Invoice date].[Date]))

After clicking on the slicer I am not able to get the answer as it is taking a lot of time to load and sometimes result in an error, and the same time the other measures are working pretty fine!

The error name: There's not enough memory to complete this operation. Please try again later when there may be more memory available.

[Pragati11]

Hi @Nimai123 ,

I have created a sample data and I am attaching a sample .pbix file for this. Refer the following link:

                                                                                                                        https://we.tl/t-ov0dug5B6N

I have created filteres for Year and Month on this report, just try out if this solution works for you.

Also, in my sample data, the date field is within my sample file. In measures that I created, I have used the same. These can be replaced with the date columns coming from your CALENDAR DATE table.

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[MFelix]

Hi @Nimai123 ,

This is an issue related with nested SUMX.

Check the blog post  below with an explanation and ways to surpass this.

https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/

[Nimai123]

If I am using SUM instead of SUMX as below to the above DAX I get the data which is not proper.

This Year Test = SUMX(Main,TOTALYTD(SUM('Main'[Sales]),'Invoice Date'[Invoice date].[Date]))

The problem which I am facing is that if I use SUM on the DAX and when I use slicers on Months I get the data of the total of the months

For Example: If I select February I get the value with the sum of January and February where I should be getting the data of only February. To eliminate this error I used SUM after the TOTALYTD which results in garbage or wrong value.

[amitchandak · Accepted solution]

@Nimai123 

Why are using sumx two time. You are using totalYTD that will sum of Jan and feb.  TotalMTD give sum of month

Also prefer creating a date table and use that to join

Try like

This Year Test = TOTALYTD(SUM(Table[Sales]),'Invoice Date'[Invoice date])
This Year Test = TOTALYTD(SUM(Table[Sales]),'Date'[Date])

And if [Sales] is alreay a measure 

This Year Test = TOTALYTD(([Sales]),'Invoice Date'[Invoice date])
This Year Test = TOTALYTD(([Sales]),'Date'[Date])

You can use datesytd and totalytd , few examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Nimai123]

I tried using 

YTD Sales = CALCULATE(SUM(Main[Sales]),DATESYTD(('Invoice Date'[Invoice date].[Date]),"12/31"))

And 

Last YTD Sales = CALCULATE([Total Cost Sumx],DATESYTD(dateadd('Invoice Date'[Invoice date].[Date],-1,Year),"12/31"))

I am getting YTD Sales Correct but Wrong output in Last YTD sales, and by using this I am not able to get the sales of the month data as I select May it Sums up all the above months instead of showing the output of only May.

And this measure is not working for the range filter.

This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Invoice Date'[Invoice date].[Date], 0, year))

Last Year Sales = CALCULATE([This Year Sales],DATEADD('Invoice Date'[Invoice date].[Date], -1,YEAR))

Total Sales Sumx = SUMX(Main,(Main[Sales]))

In the above scenario Month, wise is working well shown in the below image but is not working when I use Range filter 

And my Date column has a relationship with the date column of the main table.

 

 My Aim is to calculate This year sales and Last Year sales and filter it depending upon the Year, Month, and Range Slicer(Between)

@amitchandak 

[amitchandak]

Can you share sample data and sample output.  Mark me @

[Nimai123]

My pbix file is large and I will be unable to share it, is it possible that i can show you on teams meeting.

@amitchandak 

[Nimai123]

I am not able to post a reply due to some error I will be replying you on your personal Chat please have a look 

Thanks in Advance

@amitchandak 

[Nimai123]

I tried using 

YTD Sales = CALCULATE(SUM(Main[Sales]),DATESYTD(('Invoice Date'[Invoice date].[Date]),"12/31"))

And 

Last YTD Sales = CALCULATE([Total Cost Sumx],DATESYTD(dateadd('Invoice Date'[Invoice date].[Date],-1,Year),"12/31"))

I am getting YTD Sales Correct but Wrong output in Last YTD sales, and by using this I am not able to get the sales of the month data as I select May it Sums up all the above months instead of showing the output of only May.

 And this measure is not working for the range filter.

This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Invoice Date'[Invoice date].[Date], 0, year))

Last Year Sales = CALCULATE([This Year Sales],DATEADD('Invoice Date'[Invoice date].[Date], -1,YEAR))

Total Sales Sumx = SUMX(Main,(Main[Sales]))

 In the above scenario Month, wise is working well shown in the below image but is not working when I use Range filter as shown in the above image.

 

And my Date column has a relationship with the date column of the main table.

 

 My Aim is to calculate This year sales and Last Year sales and filter it depending upon the Year, Month, and Range Slicer(Between)

@amitchandak