cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Prev Value if Blank

Hi, I have this cumulative calculation with the x axis being a month. If I don't have a value in one month, my line breaks. Any idea how to amend the existing logic so it pulls previous value is there's a blank?

Val =

calculate

(sum(test_val),

filter(

Allselected (test),

test[test_date] <= max (test[test_date])),

groupby (test, test[test_year]))

2 REPLIES 2
Super User

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number
2. Sort the Month name column by the Month number column
3. Create a relationship (Many to One and Single) from the test_date column to the Date column of the Calendar Table
4. To yoru visual, drag Year and Month name from the Calendar Table
5. Write these measures

Total = sum(test[test_val])

Total YTD = calculate([Total],datesytd(calendar[date],"31/12"))

If you want the accomulation since the inception, then write this measure

Total since inception = calculate([total],datesbetween(calendar[date],minx(all(calendar),[Calendar[date]),max(calendar[date])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @M_SBS_6 ,

You can try this DAX:

``````Val =
VAR CurrentDate = MAX(test[test_date])
VAR PreviousValue = CALCULATE(
SUM(test[test_val]),
FILTER(
ALLSELECTED(test),
test[test_date] < CurrentDate
),
GROUPBY(test, test[test_year]),
LASTNONBLANK(test[test_date], CALCULATE(SUM(test[test_val])))
)
VAR CurrentValue = CALCULATE(
SUM(test[test_val]),
FILTER(
ALLSELECTED(test),
test[test_date] <= CurrentDate
),
GROUPBY(test, test[test_year])
)
RETURN IF(ISBLANK(CurrentValue), PreviousValue, CurrentValue)``````

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors