Previousmonth with Calculate and Userelationship

[mariases94]

Hi,

I need help with my model. I try to compare the leads we have won this month with the last month. I use the next formula:

PrevMonth WIN LEADS = CALCULATE(Opo[Leads ganados], PREVIOUSMONTH(dateadd(Opo[Fecha de cierre real],-1,month)),

USERELATIONSHIP(Opo[Fecha de cierre real],Calendario[Date]))

But if I use this formula, it shows me a BLANK.

Where is the mistake? Is in the formula? Maybe in the relationships?

Thanks for the help.

[ryan_mayu]

@mariases94 

https://docs.microsoft.com/en-us/dax/previousmonth-function-dax

https://docs.microsoft.com/en-us/dax/dateadd-function-dax

use either previousmonth or dateadd.  

[mariases94]

Hi @ryan_mayu,

I use the two formulas, but it return me blanks again...how can I solve it?

[ryan_mayu]

@mariases94 

what's the formula you are using now?

maybe you can try

PrevMonth WIN LEADS = CALCULATE(Opo[Leads ganados],dateadd(Calendario[Date],-1,month),

USERELATIONSHIP(Opo[Fecha de cierre real],Calendario[Date]))

[lbendlin · Accepted solution]

PREVIOUSMONTH(dateadd(Opo[Fecha de cierre real],-1,month))

means "go back one month and then take ALL DATES from the month before"

use 

PREVIOUSMONTH(Opo[Fecha de cierre real])

instead if you want ALL DATES from the previous month,

or 

dateadd(Opo[Fecha de cierre real],-1,month)

if you want to shift all the dates by a month into the past.

Power BI DAX: DATEADD versus PREVIOUSMONTH – Minova

[lbendlin]

You are basically applying Previous month twice.  What made you choose USERELATIONSHIP?  It is not required here.

[mariases94]

Hi @lbendlin,

I can't saw where I use two times "previousmonth".

I have 5 date columns in the principal table, so I made 5 relations between de column "date" of the calendar table with each column type date of the principal table. I use USERELATIONSHIP because I have to use the relation with the other date column. For example, if I want to use "fecha alta" I use USERELATIONSHIP to switch the relation. Because I don't know how to pick another column type date if I don't use USERELATIONSHIP...

**I have to say that I use PBI since may and I'm noobie with it and there are some things that I don't understand at all.

Thanks,

Maria.