Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Uzi2019
Super User
Super User

Incorrect Previous month total

Hi Experts,

 

I want to show percentage of previous month. After creating formula 

Previous Cost =
var prev= CALCULATE(SUM(Channel[Cost]),DATEADD(Channel[date],-1,MONTH))
var curr= CALCULATE(SUM(Channel[Cost]))
var Ans= IFERROR(((curr-prev)/prev),"-")
return Ans
Row wise it is showing correct answer but total showing incorrect value.
 
 
 
 
 
 

Capture.PNG

prev got this value instead of 268642.83  total it is showing 2601461.69.

 

Capture.PNG

 I dont know why I am getting incorrect result in Total only. row wise showing correct value.
 
Correct answer should be "-0.34" in "Previous cost total"
(176062.52-268642.83)/268642.83= -0.34
 
Please help me to solve this issue.
Thank you in advance.
Don't forget to give thumbs up and accept this as a solution if it helped you!!!
1 ACCEPTED SOLUTION

Hi, @Uzi2019 

According to your picture and description, I guess that you want to make the total row of [prev] display the correct total value, you can create a new measure [prev_new]:

prev_new =

var _new=SUMMARIZE('Table','Table'[date],"_value",[prev])

return IF(HASONEVALUE('Table'[date]),[prev],SUMX(_new,[_value]))

You should replace the ‘Table’ with your table name, and place this measure into the table chart.

And you can get what you want.

 

If this result is not what you want, you can post some sample data(without sensitive data) so that we can help you in advance.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@Uzi2019 , dateadd need the continuous date, so if the date is not present in table or last month the same date is not present it will give the wrong answer. That is why we need to use date table, which has continous date

 

example, see for the date where I do not have one month old dates. Any mssing date in this month and last month can casue diff

Screenshot 2020-11-11 14.22.07.png

Hi @amitchandak ,

I have continuous dates. Thats why Row wise i got correct answer. only problem is with Total. 

Uzi2019_0-1605169281218.png

See above data is there for 1 2 and 3rd oct and nov. but still got incorrect total.

Uzi2019_1-1605169367119.png

See Row wise prev is correct only total is incorrect. it sould be 268642.83.

I have also created same thing using calendar table still getting incorrect and blank answer.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Hi, @Uzi2019 

According to your picture and description, I guess that you want to make the total row of [prev] display the correct total value, you can create a new measure [prev_new]:

prev_new =

var _new=SUMMARIZE('Table','Table'[date],"_value",[prev])

return IF(HASONEVALUE('Table'[date]),[prev],SUMX(_new,[_value]))

You should replace the ‘Table’ with your table name, and place this measure into the table chart.

And you can get what you want.

 

If this result is not what you want, you can post some sample data(without sensitive data) so that we can help you in advance.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

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

amitchandak
Super User
Super User

@Uzi2019 , for time intelligence use date table, any missing date will cause the issue

 

var prev= CALCULATE(SUM(Channel[Cost]),DATEADD('DATE'[date],-1,MONTH))
var curr= CALCULATE(SUM(Channel[Cost]))
var Ans= Divide([curr]-[prev]),[prev],0) //Avoid - as that will convert it into a text , use divide
 
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 :radacad sqlbi My Video Series Appreciate your Kudos.
 

Hi @amitchandak ,

Thanks for your reply.

As I mentioned, I am getting incorrect Prev Month Total which leads to incorrect MoM Growth. So first we have to rectify previous month figure. Individual rows are coming correctly but total is incorrect.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.