# Error calculating Cumulative Totals

Hi there!

I am going crazy trying to solve this puzzle: I need to calculate cumulative totals withing a giving period of time (Month Year Slicer). I have figure out this measure:

``````Cumulative VALUE =
VAR _Lastdate = CALCULATE( LASTDATE('FactTable'[Date] ); ALL('FactTable') )
VAR _Firstdate = CALCULATE( STARTOFYEAR('FactTable'[Date] ) )

RETURN
IF(
SELECTEDVALUE( CALENDAR[Date] ) > _Lastdate;
BLANK();
CALCULATE(
SUM(
[ColumnA];
FILTER(
ALL( CALENDARIO );
CALENDARIO[Date] >= _Firstdate &&
CALENDARIO[Date] <= MAX( CALENDAR[Date] )
)
)
``````

It works fine when the fact table have values for the specific MonthYear selected on the slicer. Problem is when fact table doesnâ€™t have any value for than giving period of time. In this case, measures gives me back whole total instead of cumulative.

Any ideas?

Hello @judy_fmf

Thank You for posting your query onto the Forum.

The measures are giving you back the total of whole time because the under the Filter part the condition is applied as â€śALLâ€ť instead of â€śALLSELECTEDâ€ť. When we mention â€śALLâ€ť it ignores the entire context and gives us the overall result inspite of selections made into the slicer/filter.

Can you give this formula a try as mentioned below -

``````Cumulative VALUE =
VAR _Lastdate = CALCULATE( LASTDATE('FactTable'[Date] ); ALL('FactTable') )
VAR _Firstdate = CALCULATE( STARTOFYEAR('FactTable'[Date] ) )

RETURN
IF(
SELECTEDVALUE( CALENDAR[Date] ) > _Lastdate;
BLANK();
CALCULATE(
SUM(
[ColumnA];
FILTER(
ALLSELECTED( CALENDARIO );
CALENDARIO[Date] >= _Firstdate &&
CALENDARIO[Date] <= MAX( CALENDAR[Date] )
)
)
``````

Iâ€™m also providing links of the videos of difference between â€śALLâ€ť and â€śALLSELECTEDâ€ť.

Hoping this helps you.

Thanks & Warm Regards,
Harsh

Hi @Harsh,

First of all, thanks for your quick answer though it hasnâ€™t worked. If I use ALLSELECTED it would give me back the result for the Month Year Selected on the slicer, not the cumulative value from the beggining of the year.

It is strange becouse I am using the same patterns in other fact tables and my original measure works fine. Also, when the measure is applied in a month with values it also works fine. It only does not work for those months that donâ€™t have valueâ€¦

Hi @judy_fmf,

Can you give this a try

``````Cumulative VALUE =
VAR _Lastdate = CALCULATE( LASTDATE('FactTable'[Date] ); ALL('FactTable') )
RETURN

IF(
SELECTEDVALUE( CALENDAR[Date] ) > _Lastdate;
BLANK();
TOTALYTD( SUM( [ColumnA] ); CALENDAR[Date] )
)
``````

.

1 Like

Thanks @Melissa but unfortunately didnâ€™t work.

I think the problem is on the data model. I have realized that the fact table does not have values for that month:

I have worked out this measure but it still wonâ€™t work

``````Cumulative Value =
VAR _Lastdate = CALCULATE( LASTDATE('FACT TABLE'[Date] ); ALL('FACT TABLE') )
VAR _Firstdate = CALCULATE( STARTOFYEAR('FACT TABLE'[Date] ) )
VAR _MaxDate =
IF(
MAX('FACT TABLE'[Date] ) = BLANK();
MAX( CALENDAR[Date] );
MAX('FACT TABLE'[Date] )
)

RETURN
IF(
SELECTEDVALUE( CALENDARIO[Date] ) > _Lastdate ;
BLANK() ;
CALCULATE(
SUM( [Column A] ) ;
FILTER(
ALL( CALENDAR ) ;
CALENDAR[Date] >= _Firstdate &&

CALENDAR[Date] < _MaxDate
)
)
)``````

hi, this YTD calculation you will need to have ALL at your VAR _firstdate, the purpose of using VAR is creating a new context outside the row context youâ€™re currently on.

Iâ€™m not too fancy as dax on top of my head, but try this:

YTD Total PO Receipt Value USD =
VAR GetCurrentYear =
MAXX ( FILTER(Fiscal_Calendar,Fiscal_Calendar[FC Day Offset]<=0),Fiscal_Calendar[FC Year] )
VAR EndDate =
MAX(Fiscal_Calendar[Calendar Date])
VAR StartDate =
CALCULATE (
MINX (
FILTER ( ALL (Fiscal_Calendar), Fiscal_Calendar[FC Year] = GetCurrentYear ),
Fiscal_Calendar[Calendar Date]
)
)
RETURN
CALCULATE (
[Total PO Receipt Value USD],
Fiscal_Calendar[Calendar Date] >= StartDate,
Fiscal_Calendar[Calendar Date] <= EndDate
)

Hi @judy_fmf,

If youâ€™re still experiencing issues please provide a sample PBIX, so members can better assist you. Thanks!

I restart and refresh and now your solution it is working smoothly. I am so happy and greatful.
Thanks again for your great work and help