Daily change of target based on month

[mvyskala]

Hello all,

I have table with monthly targets (see below) and I want to change them monthly and daily. Basically if its 6th of June it should sum all previous targets from months 3, 4 and 5, and add 6/30 out of June. Hope it makes sense.

Any idea how I could do this? I'm quite new to DAX so any idea will be appreciated.

Thanks in advance.

Month	Target
3	7035701
4	6863604
5	7065030
6	5961995
7	5936737
8	7369743
9	6657633
10	7746493
11	12791118
12	13439972
1	7592770
2	6235776

[v-shex-msft · Accepted solution]

Hi @mvyskala,

According to your description, you want to get the running total target, right?

If this is a case, you can try to use below formula to achieve your requirement:

Calculate Column:

Running Target = 
var previous= SUMX(FILTER(ALL(Sheet1),[Month]<EARLIER('DateTable'[Date].[MonthNo])),[Target])
var daysOfCurrMonth=DAY(DATE([Date].[Year],[Date].[MonthNo]+1,1)-1)
var curr=LOOKUPVALUE(Sheet1[Target],Sheet1[Month],[Date].[MonthNo])/daysOfCurrMonth*[Date].[Day]
return
previous+curr

Measure

Total Target = 
var currDate=MAX(DateTable[Date])
var previous= SUMX(FILTER(ALL(Sheet1),[Month]<currDate),[Target])
var daysOfCurrMonth=DAY(DATE(YEAR(currDate),MONTH(currDate)+1,1)-1)
var curr=LOOKUPVALUE(Sheet1[Target],Sheet1[Month],MONTH(currDate))/daysOfCurrMonth*DAY(currDate)
return
previous+curr

Regards,

Xiaoxin Sheng