Forum Discussion
sum values with the last date (month)
- 9 years ago
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)
Month Index should be a COLUMN not Measure!
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 )
)
- Sean9 years ago
Community Champion
Add the - 1 to this formula you had posted before in a picture
SumLatest = SUMX ( FILTER ( Tempo; Tempo[Data] = MAX ( Tempo[Data] ) - 1 ); [%] )
- Twister89 years ago
Helper II
- Twister89 years ago
Helper II
Considering this scenario my card need show 1, because the actual month is november and last month is october and the value is 1
- Sean9 years ago
Community Champion
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
- Twister89 years ago
Helper II
Sorry but i dont understand where is the erro in my creation...
- Sean9 years ago
Community Champion
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?
- Twister89 years ago
Helper II
In Fact Table I have a column Date its key for table Time, so the relationship its Fatc(Date) and Time(Date)
- Sean9 years ago
Community Champion
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)
- Twister89 years ago
Helper II
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)