PREVIOUSMONTH + FILTER return blank

[Anonymous]

Hi Experts

I tried to use a combination of PREVIOUSMONTH and FILTER, but only got blank. It works well when only one filter is applied.

I went through related topics here and tested couple of solutions like ALL.

With All applied I can see value returned, but all remain the same at different level, apparantly not acceptable.

Below is how I wrote it:

Reserve@GC-1 = CALCULATE(SUM(TotalReserve[Total Value (GC)]),PREVIOUSMONTH('TIME'[MonthDate]),FILTER(TotalReserve,TotalReserve[Type]="Reserve"))

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

Reserve@GC-1 = CALCULATE(SUM(TotalReserve[Total Value (GC)]),PREVIOUSMONTH('TIME'[MonthDate]),TotalReserve[Type]="Reserve")

[Anonymous]

Hi Ashish, it works! I didn't see PREVIOUSMONTH function can support 2nd expression before, even on MS DOCS.

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi Ashish

After the formula applied I noticed that the tabel subtotal returns something very exaggerated.

It didn't return simple column sum as expected, especially the last column the result is way too big, like multiplied by somthing. Do you have any idea on this?Thanks.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please also show the expected result clearly.

[amitchandak]

@Anonymous , make sure time table marked as date tbale and try a formula like

Reserve@GC-1 = CALCULATE(SUM(TotalReserve[Total Value (GC)]),DATESMTD(dateadd('Time'[MonthDate],-1,MONTH),FILTER(TotalReserve,TotalReserve[Type]="Reserve"))

also use month date from Time table in visual

[Anonymous]

Thanks Amitchandak, time table is in date formate, but the result remains. Per my understanding DATESMTD seemly  works the same way as PREVIOUSMONTH. 

[amitchandak]

@Anonymous ,Can you share a sample pbix after removing sensitive data?

Refer to my video for 5 reasons time intelligence can fail - https://www.youtube.com/watch?v=OBf0rjpp5Hw

[Anonymous]

Hi Amit

The raw data is huge with indeed lots of sensitive info, not handy to share here.

But I have attached the model above, very simple with only 3 tables: 2 master and 1 fact.

Wonder if it possible to digest over this? You may ask me anything and I will reply shortly.