Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there, my cumulative sum is extending to further dates where there are blank values. I need it to stop when there is no more data. I am specifically looking at the green line in the chart on the left and the red/green lines on the right
Thank you
Solved! Go to Solution.
I got it. here is what I did (note table names are slightly different from original). I am guessing it is because my primary table had additional dates beyond the max target date, so I included a filter on the original max date calculation for "target" only. Thank you for your help
Original Baseline Weekly2 =
VAR MaxDate = CALCULATE(MAX(fact_ManhourProgress[Date]),FILTER(fact_ManhourProgress,fact_ManhourProgress[Type]="Target"))
RETURN
CALCULATE (
SUM ( fact_ManhourProgress[Value] ),
FILTER (
ALLSELECTED ( dim_Date ),
dim_Date[Date] <= MaxDate
),
FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Target" )
)
Hi @Anonymous ,
Try to add the following code:
Baseline Weekly =
IF (
MAX ( dim_Date[CurWeekOffset] )
>= CALCULATE ( MAX ( fact_Progress[Date] ), ALL ( fact_Progress[Date] ) ),
BLANK (),
CALCULATE (
SUM ( fact_Progress[Value] ),
FILTER (
ALLSELECTED ( dim_Date ),
dim_Date[CurWeekOffset] <= MAX ( dim_Date[CurWeekOffset] )
),
FILTER ( ALLSELECTED ( fact_Progress ), fact_Progress[Type] = "Target" )
)
)
Replace the fact_Progress[Date] by the column date of your fact_progress.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI can't share a sample file. The current week offset of my dim_date table is a numeric number based on the # of days from present (yesterday is -1, today is 0, tomorrow is +1). Could this be the problem with your formula?
Hi @Anonymous ,
Without any other information about your model is difficult to help you.
Can you share the way your model is setup and a sample of the information so I can make some tests?
If you don't want to share the file because it's sensitive information you can do it through private message or make a mockup file with the structure and sample data.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI got it. here is what I did (note table names are slightly different from original). I am guessing it is because my primary table had additional dates beyond the max target date, so I included a filter on the original max date calculation for "target" only. Thank you for your help
Original Baseline Weekly2 =
VAR MaxDate = CALCULATE(MAX(fact_ManhourProgress[Date]),FILTER(fact_ManhourProgress,fact_ManhourProgress[Type]="Target"))
RETURN
CALCULATE (
SUM ( fact_ManhourProgress[Value] ),
FILTER (
ALLSELECTED ( dim_Date ),
dim_Date[Date] <= MaxDate
),
FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Target" )
)
User | Count |
---|---|
94 | |
78 | |
73 | |
64 | |
60 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |