topic Re: calculating difference in DAX Commands and Tips

calculating difference

Anonymous — Thu, 15 Sep 2022 14:52:26 GMT

Hi,

I am stucked with the following question.

Product Code	Sales	Date
A	10	01/02/2022
B	10	01/02/2022
C	10	01/02/2022
D	10	01/02/2022
A	10	01/03/2022
A	10	01/03/2022
B	10	01/03/2022
C	10	01/03/2022
A	10	01/04/2022
B	10	01/04/2022

I would look for a equation that can calculate the difference between two period of time. In this data set every month I will update the sales record and I need a measure that can gives me somthing like below:

Assume today is Apr:

Product Code	Total Sales	Difference vs Last month
A	40	10
B	30	10
C	20	0
D	10	0

What I need is the measure that can complete "Difference vs Last month" at the same time it does not require filter on visualization.

I can do this by using two spreadsheet inside powerBI, however, I hope this can be on 1 spreadsheet so everytime I dont have to refresh two file.

Is there any way to do this?

Thank you!

Re: calculating difference

NyeinChan — Thu, 15 Sep 2022 14:57:53 GMT

You can use either time intelligence build in function or just create common dimension column (i.e. month for you) and create a measure calculating sale difference based on current vs previous period.

Re: calculating difference

PabloDeheza — Thu, 15 Sep 2022 15:00:11 GMT

Hey there!

Try this:

MoM Revenue Growth = VAR _Actual = Total VAR _PrevMonth = CALCULATE( Total, PREVIOUSMONTH( YourDateColumn ) ) RETURN _Actual - _ PrevMonth

Let me know if that helps!

Re: calculating difference

tamerj1 — Thu, 15 Sep 2022 15:03:20 GMT

Hi Anonymous
What is the difference?

Re: calculating difference

Anonymous — Fri, 16 Sep 2022 06:45:55 GMT

Hi,

I am not sure what's wrong with my equation as it keep return error message.

MoM Revenue Growth =

VAR _Actual =

sum('Table'[Sales])

VAR _PrevMonth =

CALCULATE(

CALCULATE(sum('Table'[Sales])),

PREVIOUSMONTH ('Table'[Date])

)

RETURN

_Actual - _ PrevMonth

Do you know what I did wrong?

Thanks!

Re: calculating difference

PabloDeheza — Fri, 16 Sep 2022 12:35:07 GMT

Can you please provide the error message you are getting? Thanks

Re: calculating difference

Anonymous — Mon, 19 Sep 2022 10:35:32 GMT

The error message is like below:

The syntax for 'PrevMonth' is incorrect. (DAX(VAR _Actual = sum(''Table'[Sales])VAR _PrevMonth = CALCULATE( CALCULATE(sum('Table'[Sales])), PREVIOUSMONTH ('Table'[Date]) )RETURN _Actual - _ PrevMonth)).

Re: calculating difference

tamerj1 — Mon, 19 Sep 2022 11:21:58 GMT

Anonymous

You have a space before PrevMonth. Delete it

Re: calculating difference

Anonymous — Tue, 20 Sep 2022 01:35:18 GMT

tamerj1

Hi, the equation is working now but it only gives the sum of latest month data, I believe shuld be the PREVIOUSMONTH setting not working probaly, any advice on this?

Or can this criteria set as calculate the sum of the value except a sepecific date? Will that be easier?

Thanks!

Re: calculating difference

Anonymous — Tue, 20 Sep 2022 08:09:40 GMT

Hi, I have fixed the issue and the equation is now working.

Thank you very much!