How to calculate the sum of the last 6 month?

[micwed]

Hi,

I am very new to DAX and it it is realy hard to understand fom me. Maybe somebody can give me an example or link to the answer of the same question. 

I want to calculate the sum of quantity of the last 6 month

Usualy I would try as in any other language: Measure = CALCULATE([Sum of Quantity], 'Date'[Date]>=DATEADD(TODAY(),-6,month))

It should be a very simple formular, honestly it isn't.

How can I do this, without adding claculated columns just within a measure. Just in one measure.

Thanks in advance!

Mike

[Greg_Deckler]

@micwed Try this:

Last 6 Months Measure =
  VAR __Today = TODAY()
  VAR __SixMonthsAgo = EOMONTH(__Today,-7) + 1
  VAR __Table = FILTER('Table',[Date]>=__SixMonthsAgo && [Date]<=__Today)
  VAR __Result = SUMX(__Table,[Quantity])
RETURN
  __Result

[micwed]

Hi Greg,

thank you very much for your answer!

After a wile I found a solution that fits to my needs.

I used

Sum of Quantity = SUM(Sales[Quantity])

Sold Quantity = CALCULATE([Sum of Quantity],DATESINPERIOD('Date'[Date],TODAY(),-6, MONTH))

Yes of course this is rolling month, but its okay in this case. Is there any advantagas or disadvantages on your way or my way?

Best regards,

Mike

[belanoor]

I'm exceptionally new to DAX and it is realy hard to comprehend fom me. Perhaps someone can offer me a model or connection to the response of a similar inquiry.

[Greg_Deckler]

@belanoor @micwed Can you provide sample data. By way of explanation in terms of the formula:

Last 6 Months Measure =
  VAR __Today = TODAY()
// Get today's date

  VAR __SixMonthsAgo = EOMONTH(__Today,-7) + 1 
// Goes back 7 months (end of month) + 1 is the first of the month 6 months ago

  VAR __Table = FILTER('Table',[Date]>=__SixMonthsAgo && [Date]<=__Today) 
// filter the table such that all dates between today's date and six months ago

  VAR __Result = SUMX(__Table,[Quantity])
// Sum up the Quantity column for all rows

RETURN
  __Result

[micwed]

Hi Greg,

I just saw, that probably I can't use your solution, because I have my data stored in a star-schema and have no generic Date column in my Sales data, but you assume it, right?

Here just a small part of my model

Best,

Mike