Forum Discussion
Solved
Populating future dates with previous year and same month results?
Hello my data is something like the below and i want to create a New Pols column, i tried the following formula but it doesn't populate future dates only populates the dates less than today...
Parallel = IF(Query1[ActivityDate]<NOW(), Query1[POLCNT], CALCULATE(SUM(Query1[POLCNT]),FILTER(Query1,Query1[Month]=MONTH(Query1[ActivityDate])),FILTER(Query1,Query1[Year]=YEAR(Query1[ActivityDate])-1))*1.2)
| State | Rep | Channel | activityDate | year | month | day | Polcnt | Parallel |
| x | A | M | 1/1/2010 | 2010 | 1 | 1 | 1 | 1 |
| x | B | K | 1/1/2011 | 2011 | 1 | 1 | 2 | 2 |
| x | A | L | 1/1/2012 | 2012 | 1 | 1 | 3 | 3 |
| y | C | K | 1/1/2013 | 2013 | 1 | 1 | 4 | 4 |
| y | D | L | 12/1/2017 | 2017 | 12 | 1 | 5 | 5 |
| y | E | N | 12/1/2018 | 2018 | 12 | 1 | 6 | 7.20 |
Hi tahir9
I've added in the additional checks for those columns (highligted the changes in red-bold)
Parallel = VAR MyDate = DATE('Table'[year],'Table'[month],1) VAR SumOfMonthLastYear = SUMX( FILTER( 'Table1', 'Table'[year] = EARLIER('Table'[year]) - 1 && 'Table'[month] = EARLIER('Table'[month]) && 'Table'[State] = EARLIER('Table'[State]) && 'Table'[Rep] = EARLIER('Table'[Rep]) && 'Table'[Channel] = EARLIER('Table'[Channel]) ), 'Table'[Polcnt] ) RETURN IF( MyDate< TODAY() , --- THEN --- 'Table'[Polcnt] , --- ELSE --- SumOfMonthLastYear * 1.2 )
9 Replies
HI tahir9
What is the number you are after in the Parallel column for the bottom row? Is 7.20 the number you want? Or is this the output of the calculation that isn't working how you would like?
- Yeah the 7.2 which is just the 6 multiplied by 1.2.
Is this calculated column close?
Parallel = VAR MyDate = DATE('Table1'[year],'Table1'[month],1) RETURN IF( MyDate< TODAY() , --- THEN --- 'Table1'[Polcnt] , --- ELSE --- 'Table1'[Polcnt] * 1.2 )
