DAX

[AmalrajRRD1]

Hi all, 

 How to do Privous month calculation like i have current month (100)-privous month (50) = 50

how to do in dax.. 

Thanks

[TomMartens · Accepted solution]

Hey,

do you have a separate Calendar table, if not you should 😉 And make sure both tables are related properly.

Then you should give PREVIOUSMONTH a try like so:

CALCULATE(SUM('yourtable'[yournumericcolumn]), PREVIOUSMONTH('calendar'[Date]))

Here you will find almost everything about time intelligence with DAX:

http://www.daxpatterns.com/time-patterns/

If you are new to the sometimes mind-boggling DAX statements I would wholeheartedly recommend the following books

 also in this sequence

1. Analyzing Data with Microsoft Power BI and Power Pivot for Excel
2. Dax Patterns 2015
3. The Definitive Guide to DAX

All three have been written by Marco Russo and Alberto Ferrari

Hope this gets you started 

[TomMartens · Accepted solution]

Hey,

do you have a separate Calendar table, if not you should 😉 And make sure both tables are related properly.

Then you should give PREVIOUSMONTH a try like so:

CALCULATE(SUM('yourtable'[yournumericcolumn]), PREVIOUSMONTH('calendar'[Date]))

Here you will find almost everything about time intelligence with DAX:

http://www.daxpatterns.com/time-patterns/

If you are new to the sometimes mind-boggling DAX statements I would wholeheartedly recommend the following books

 also in this sequence

1. Analyzing Data with Microsoft Power BI and Power Pivot for Excel
2. Dax Patterns 2015
3. The Definitive Guide to DAX

All three have been written by Marco Russo and Alberto Ferrari

Hope this gets you started 

[AmalrajRRD1]

here i can see the differences right?

Thanks

[TomMartens]

No, my example just gets you to calculate the value for the previous month, finally you have to do the subtraction 🙂

[AmalrajRRD1]

How to do the subtraction .

can you help me on this

[TomMartens · Accepted solution]

The subtraction can look like this

CALCULATE(SUM('yourtable'[yournumericcolumn])) - CALCULATE(SUM('yourtable'[yournumericcolumn],PREVIOUSMONTH('calendartable'[date]))

Cheers

[ContabilidadBI]

Hi @TomMartens,

I created a new post here: http://community.powerbi.com/t5/Desktop/Active-Clients/m-p/229032#M102144

@AmalrajRRD1,

Could you solve your problem?

as Tom says you need the two measures and then substract them. Example:

Sales = CALCULATE(SUM(Sales[SalesAmount]))
SalesPreviousMonth = CALCULATE (SUM(Sales[SalesAmount]), PREVIOUSMONTH(Sales[SalesAmount])

Diferrence= [Sales] - [SalesPreviousMonth]

If you use the difference measure in a table with months of the rows for example, each month you will see the difference bteween the sales of that month and the sales of the previous month.

Hope that helps

[ContabilidadBI]

Then you just need to filter the visualization for thoes years.

[ContabilidadBI]

Hi @TomMartens and others,

I have a simple doubt about time with DAX and I was about to create a new topic but thought that it was a better idea to ask in a related post.

I want to achieve this:

ActiveClients = CALCULATE(DISTINCTCOUNT (Sales[ClientKey]); ---BETWEEN TODAY AND 3 MONTHS AGO---)             so I want to considerate as Active clients, clients who bought something in the last 90 days for example. So if they bought something before that (and didn't buy since then), they are not active clients. I guess I need to use MONTH(TODAY) but I was trying different formulas and could not get what I wanted. Any idea how can I do that? 

Thanks!!!!!!!!

[TomMartens]

Hey,

open a new post please, and also describe your model, do you have a separate calendar table, the structure of your main table ad if possible provide easy to use sample data.

Regards