Error calculating Cumulative Totals

DAX DAX Showcase
1

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.

CumulativeTotals
CumulativeTotals770Ă—472 45.5 KB

Any ideas?

Thanks in advanced :wink:

2

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. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

3

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…

4

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] )    
)

.
I hope this is helpful.

1 Like
5

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:

CumulativeTotals2.png
CumulativeTotals2.png1200Ă—968 75 KB

I have worked out this measure but it still won’t work :frowning:

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 
         ) 
     ) 
  )
6

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
)

7

Hi @judy_fmf,

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

8

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 :slight_smile:

9

Glad I could help :+1:

1 Like