Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to Solution.
Hi,
Does this work?
Reserve@GC-1 = CALCULATE(SUM(TotalReserve[Total Value (GC)]),PREVIOUSMONTH('TIME'[MonthDate]),TotalReserve[Type]="Reserve")
Hi,
Does this work?
Reserve@GC-1 = CALCULATE(SUM(TotalReserve[Total Value (GC)]),PREVIOUSMONTH('TIME'[MonthDate]),TotalReserve[Type]="Reserve")
Hi Ashish, it works! I didn't see PREVIOUSMONTH function can support 2nd expression before, even on MS DOCS.
You are welcome.
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.
Hi,
Share the link from where i can download your PBI file. Please also show the expected result clearly.
@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
Thanks Amitchandak, time table is in date formate, but the result remains. Per my understanding DATESMTD seemly works the same way as PREVIOUSMONTH.
@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
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.