Forum Discussion

tahir9's avatar
tahir9
Frequent Visitor
8 years ago
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...    P...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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
        )
tahir9's avatar
tahir9
Frequent Visitor
8 years ago
Yes I would like to restrict it to those other variables like state/rep channel... Thanks!
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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
        )
  • tahir9's avatar
    tahir9
    Frequent Visitor
    8 years ago

    Thanks Phil this seemed to do the trick! Earlier function along with concatenation within the sumx learned something new!!! Thanks!