Forum Discussion
Helper IIsum values with the last date (month)
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)
Community ChampionThe 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
Helper IISorry 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
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)