Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |