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
Yeah the 7.2 which is just the 6 multiplied by 1.2.
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

    Sorry Phillip I shouldn't have built my table like that it has more days than just the first of the month it has every day of the month and table ends at 12/31/2018. 

     

    I did try your solution but it does not populate future dates... 

     

    What i am trying to accomplish is go through each date and say if its less than today() then give me POLCNT, however if its in the future then go back to previous year same month and get the sum of polcnt in that month based on the individual groups like the columns labeld state/channel/rep...  

     

    So if 3/2/2018 it looks at the month of march in 2017 for a state/channel/rep and then sums it to give me whatever the total was for that month in 2017... 

     

    This is just the first part of what i am trying to do just to get the future dates to populate with something but whatever i have tried, it only populates up to the current dates nothing populates to future... 

    • tahir9's avatar
      tahir9
      Frequent Visitor
      8 years ago

      Oh and multiply it by 1.2... which i figure it pretty easy if i can just get the sum... from previous year same month. Thanks!

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi tahir9

         

        This version adds a variable that looks back a year and SUMS's the POLCNT for a previous year.  It does it for every row.  Would you want it to be restricted to just the same State etc.?

         

        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'[Polcnt]
       )
    
RETURN 
    IF(
        MyDate< TODAY() ,
        --- THEN --- 
        'Table'[Polcnt] , 
        --- ELSE ---
        SumOfMonthLastYear * 1.2
        )