sum values with the last date (month)

[Twister8]

hello everyone,

I am new in PB and DAX, I need calculate the sum of values from the last month with data on my database, for example:

month      values

2016-1     1

2016-2     5

2016-3     10

2016-3     10

So I need return the 20 in my result.

[SPMoore]

Hi @Twister8

You can use the CALCULATE function with the LASTNONBLANK function as its filter argument, you will get a result of 20.

Measure = CALCULATE(SUM(Table1[value]),LASTNONBLANK(Table1[date],1))

Let me know if it works out.

Simon

[SPMoore]

@Twister8

ps - there is an excellent tutorial on LASTNONBLANK here:

http://exceleratorbi.com.au/lastnonblank-explained/

[Greg_Deckler]

I created a column like this:

Column = CONCATENATE(YEAR([month]),MONTH([month]))

switched this to a Whole Number format

and then a measure like this:

SumLatest = SUMX(FILTER(LastMonth,[Column]=MAX([Column])),[values])

[Twister8]

@Greg_Deckler

Tks for answer :), but isnt working for me 😞

What is LastMonth? function? When I type Colum = MAX[Column] I have error because Column isnt the Date column

I am try use: 

LastMonth= CALCULATE(SUM('MYtable'[Colum With Value]); PREVIOUSMONTH('MYTableTIME'[Date]))

However when I typed in my expression i can't see the result, because i dont have a total

When I use this:

LastMonth = CALCULATE([Colum With Value];DATEADD(MYTable[Date]; -1; MONTH))

the total is all months of all years, i need just last month, in my example month 03, its possible filter the actual year?

[Greg_Deckler]

LastMonth is my table name.

[Twister8]

Tks.

I need show just 1, because last month its october, but its wrong

[Sean]

@Twister8

Create a Month Index COLUMN like this...

Month Index = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) ) )

Then create this MEASURE...

Last Month Total =
CALCULATE (
    SUM ( TableName[ColumnName] ),
    FILTER ( 'Calendar', 'Calendar'[Month Index] = MAX ( 'Calendar'[Month Index] ) - 1 )
)

This MEASURE should show you only the Last Month's Total.

Hope this helps!

[Twister8]

Tks for answer..

But its hard for me, i have the error in:

Month Index = INT (CONCATENATE (YEAR('Time'[Date]); FORMAT ( 'Time'[Date]; "MM" ) ) )

Could not determine a single value for the 'Date' column in the 'Time' table. This can happen when a measure formula refers to a column containing many values, without specifying an aggregation, such as min, max, Cont or sound for a single result.

[Sean]

Month Index should be a COLUMN not Measure!

[Twister8]

more details...

I created Column Month Index2 with the key in Fct, because i cant use Date of Dimension Time (Could not determine a single value for the 'Date' column in the 'Time' table. This can happen when a measure formula refers to a column containing many values, without specifying an aggregation, such as min, max, Cont or sound for a single result.) i dont know why...

Month Index2 = INT (CONCATENATE (YEAR('Fct'[Date]); FORMAT ( 'Fct'[Date]; "MM" ) ) )
Value2 = IF('Fct'[Real] < 'Fct'[THRESHOLD_G];1;0)

Last Month Total =
CALCULATE (
SUM ('Fct'[Value2]);
FILTER ( 'Fct'; 'Fct'[Month Index] = MAX ( 'Fct'[Month Index] ) - 1 )
)

[Sean]

Add the - 1 to this formula you had posted before in a picture

SumLatest =
SUMX ( FILTER ( Tempo; Tempo[Data] = MAX ( Tempo[Data] ) - 1 ); [%] )

[Twister8]

[Twister8]

Considering this scenario my card need show 1, because the actual month is november and last month is october and the value is  1

[Sean]

The formulas I posted on the previous page actually work - here there are again

First Create this COLUMN in your Calendar Table (the error you were getting was because you were creating a Measure instead)

Month Index = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) ) )

After you create the column - Then create this MEASURE (and Yes this Measure works in a Card!)

Last Month Total =
CALCULATE (
    SUM ( TableName[ColumnName] ),
    FILTER ( 'Calendar', 'Calendar'[Month Index] = MAX ( 'Calendar'[Month Index] ) - 1 )
)

Otherwise you'll have to post a sample of your data set... not the Visuals but your data

[Twister8]

Sorry but i dont understand where is the erro in my creation...

[Sean]

How many Date columns do you have in the Fact table?

And how are the 2 tables related? What column in Fact to what column in Time?

[Twister8]

In Fact Table I have a column Date its key for table Time, so the relationship its Fatc(Date) and Time(Date)

[Sean]

So you don't have other date columns?

I can only duplicate this issue when you are using the wrong Date column in the Fact table for your relationship to the Date table.

For example if you want to SUM the Payment amounts you'll use the Payment Date (Oct 1, 2016) not Order Date (Aug 31, 2016)

Meaning the relationship has to be Payment Date column to Date column in Calendar (Not Order Date to Date)

[Twister8 · Accepted solution]

HI,

Tks everybody for answers...

I solved:

LastMonth = IF(YEAR(TODAY())= YEAR('Fact'[Date]) && MONTH(TODAY())-1 = MONTH('Fact'[Date]);1;BLANK())

Mymeasure = CALCULATE(SUM('Fact'[Value]);'Fact'[LatMonth] =1)