Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |