Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
tahir9
Frequent Visitor

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)

 

StateRepChannelactivityDateyearmonthdayPolcntParallel
xAM1/1/201020101111
xBK1/1/201120111122
xAL1/1/201220121133
yCK1/1/201320131144
yDL12/1/2017201712155
yEN12/1/201820181216          7.20

 

1 ACCEPTED SOLUTION

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
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Phil_Seamark
Microsoft Employee
Microsoft Employee

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Frequent Visitor

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!

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
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes I would like to restrict it to those other variables like state/rep channel... Thanks!

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
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.