DAX to create columns for the current month, previous month, and month last year

[Anonymous]

Hello, I need to create a column chart that shows the sum of the current month's sales, one column with sales from the previous month, and another column with sales from the same month last year.

I'm connected in a typical star schema, with a calendar table connected to multiple dimensions. As the tables are very large, I need to use DirectQuery.

To calculate the current month, I created a measure, Current Month = CALCULATE (SUM (Ft_Leases [value]); PARALLELPERIOD (Dim_Dates [datefull]; 0; MONTH))) and this returns the sum correctly.

But when I created the measure of the previous month, Previous Month = CALCULATE (SUM (Ft_Lancements [value]); PARALLELPERIOD (Dim_Datas [datafull]; -1; MONTH))), the table does not return this data.

can anybody help me?


A table without previous month

[Anonymous]

Your filters will be removing the previous month data from the table.  You might need to make use of the ALL statement.

[Anonymous]

Thanks for the feedback Ross, I am very new to the DAX language. Could you help me with the syntax?

I wrote the following formula: Prev. Month = CALCULATE([Valor Total];PARALLELPERIOD(ALL(Dim_Datas[datafull]);-1;MONTH))

With this, the result of the previous month is now the same as the current month.

[EvertonPessoa]

Hi @Anonymous,

If you have a calendar, try this for the previous month:

Prev. Month = CALCULATE(SUM([Valor Total]);DATEADD(Dim_Datas[datafull];-1; MONTH))

[Anonymous]

Hi Everton,

Unfortunately, the column still remains blank.

[EvertonPessoa]

Dim_Datas[datafull] Is your calendar table right?
Does she make relationship with the table quoted in the measure?  SUM([Valor Total])

Does this calendar have year-round dates?

[MrPingu]

---

@EvertonPessoawrote:

Dim_Datas[datafull] Is your calendar table right?
Does she make relationship with the table quoted in the measure?  SUM([Valor Total])

Does this calendar have year-round dates?

---

Is a relationship neccesary? 

[Anonymous · Accepted solution]

I was able to solve the problem, and I'll record what I did to help other people.

First, I created a measure with a date reference that takes the filters in the slicer as values:

Date Ref = DATE (ALLSELECTED (Dim_Datas [year]) ALLSELECTED (Dim_Datas [month]) 30)

In this case I created a slicer with the number of the year and another slicer with the number of the month, and these values are sent to the above code.

Second, I created a measure for the previous month:

Previ. Month =
Var prevmonth = MONTH ([Date Ref]) - 1 return
CALCULATE (
SUM (Ft_Lancements [value]);
     FILTER (ALL (Dim_Dates [month]);
Dim_Dates [month] = prevmonth
))

Third, I created another measure for the same month in the previous year:

Same Month Year Past =
Var prevyear = year ([Date Ref]) - 1 return
CALCULATE (
SUM (Ft_Lancements [value]);
     FILTER (ALL (Dim_Dates [year]);
Dim_Dates [year] = prevyear
))

Finally, a measure for the current month:

Curr. Month = CALCULATE (SUM (Ft_Lancements [value]); PARALLELPERIOD (Dim_Dates [datetime]; 0; MONTH))

I hope this information can help other people, because it was a challenge for me to get this result.

[dr3sss]

Hi! Thank you for share this information!

Everything seems to work just fine except this:

Date Ref = DATE (ALLSELECTED (Dim_Datas [year]) ALLSELECTED (Dim_Datas [month]) 30)

At first sights, i notice that it misses the  ( ; ) . On the other hand, I have no idea why it doesn't work in my case 

Date Ref = DATE (ALLSELECTED (Time[year]); ALLSELECTED (Time[MonthNumber]); 30).

I already tried with the month name and it didn't work either. Can you help me?

[Anonymous]

Sorry for the delay in responding.

By the name of the month will not work, because the [month] column is the number of the month in the calendar table.

Without a calendar table, this logic will not work.

[Anonymous]

They have the dates until 2050, and this column is in date time format.
The date table is related to the fact table.