Data only show first of the week

[NickProp28]

Dear Community,

My Income and Profit data are retrieve from DB while Target is manually key in to excel then import to report.

I would like to calculate every week and get their achievement based on monthly target. But the target value only show once on every first week of the month.

Expected outcome:
January     Income      Target        Achievement
1             10428.64     43988.00       23.71%
2             2897.48       43988.00       0.065%
3             4343.37       43988.00       0.098%
4             8034.64       43988.00       0.18%
5             3866.39       43988.00       0.087%

Attached with pbix: https://ufile.io/gekx3a4z
Appreciate for any help and advice.

[ryan_mayu · Accepted solution]

@NickProp28 

you can try to create a measure

Measure = 
var _year=SELECTEDVALUE('Calendar'[Year])
VAR _month=SELECTEDVALUE('Calendar'[Month])
VAR _target=CALCULATE(sum(REV_Target[Value]),FILTER(ALL('Calendar'),'Calendar'[Year]=_year&&'Calendar'[Month]=_month&&'Calendar'[weekinmonth]=1))
return if(ISFILTERED('Calendar'[Year])&&ISFILTERED('Calendar'[Month]),_target,sum(REV_Target[Value]))

 

[amitchandak]

@NickProp28 , Try a measure like

target measure = calculate(firstnonblankvalue(Table[week], sum(Table[target])), filter(allselected(Table),Table[Month] =max(Table[Month]) && Table[year] =max(Table[year])))

[NickProp28]

Dear @amitchandak @ryan_mayu ,

Appreciate your help!
Would like to request some help, how do I accumulate the income for only that month.

For January is correct. But I dont want keep on adding to the next month.
Would like to reset the accumulate when there's a new month.

Thanks.

[ryan_mayu]

@NickProp28 

you can try this

Measure 2 = 
var _year=SELECTEDVALUE('Calendar'[Year])
VAR _month=SELECTEDVALUE('Calendar'[Month])
VAR _week=SELECTEDVALUE('Calendar'[weekinmonth])
VAR income=CALCULATE(sum('Tikcet PL'[Income]),FILTER(ALL('Calendar'),'Calendar'[Year]=_year&&'Calendar'[Month]=_month&&'Calendar'[weekinmonth]<=_week))
return if(ISFILTERED('Calendar'[weekinmonth]),income,sum('Tikcet PL'[Income]))