Forum Discussion

MrMani's avatar
MrMani
Frequent Visitor
2 years ago
Solved

First target from cumulative total

Need to help to find the first target from cumulative total.

and then highlight the months to achieve target

 

Lets set Target as 15

Measures:

"Pts Run" is a cumulative total

"Pts Tgt" = MIN (Pts Run,15)

 

Expected out put 

For id 1, it's acheived on April, Flag value is "Y" till April, remaining months to be marked as "N"

For id 2, it's acheived on March,Flag value is "Y" till March, remaining months to be marked as "N"

 

Unable to upload the sample pbix file. Pls find the details

 

Sample Data:

Table Name: Sample

iddtpts
11/1/20243
12/1/20244
13/1/20242
14/1/20247
15/1/20246
16/1/20242
21/1/20246
22/1/20244
23/1/20247
24/1/20241
25/1/20243
26/1/20245

 

Calendar table

Calendar = CALENDAR(MIN('Sample'[dt]),MAX('Sample'[dt]))
 
Measures:
 
Pts = SUM('Sample'[pts])
Pts Run = CALCULATE([Pts], ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date]))
Pts Tgt = MIN([Pts Run],15)
Pts Flg = if([Pts Tgt]<=15,"Y","N")