Missing values in cumulative dax

[josuesanchezSA]

Hello, 

I am trying to get a cumulative total which as you can see below it works, except in period 05 and 09, the totals should be 68 for period 05 and 108 for period 09. Since there were no values found for that period it gives you a zero. Can you please assist. 

Measure 2 = 
var startyear = DATE(2022,10,01)
var enddate = MAX(Table1[Date Entered])-365
return 
IF(CALCULATE(COUNTA('Table1'[ID]),DATESBETWEEN('Calendar'[Date],startyear,enddate))=BLANK()
,0,
CALCULATE(COUNTA('Table1'[ID]),DATESBETWEEN('Calendar'[Date],startyear,enddate)))

[Anonymous · Accepted solution]

Hi @josuesanchezSA ,

Based on the description, try the following methods:

Measure 2 = 
VAR startyear = DATE(2023, 01, 01)
VAR enddate = MAX('Table1'[Date]) - 365
VAR CurrentDate = MAX('Table'[Date])
var _period = SELECTEDVALUE(Table1[Month Period])
var result = CALCULATE(
    COUNTA('Table1'[ID]),
    FILTER(
        ALL('Table'),
        'Table'[Date] <= CurrentDate
            && 'Table'[Date] >= startyear
            && 'Table'[Date] < enddate
    )

)
RETURN
IF(ISBLANK(result) || _period > 9, 0 , result)

Best Regards,

Wisdom Wu

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

[josuesanchezSA]

Hello, 

That takes me back where i started, however i do want to say that what is causing this error is that for period 5 there are no values, however, if its its a cumulative total, the result should be 68 not 0

[josuesanchezSA]

Hello @aduguid 

The original formula return ineffective results, however i modify this symbol and it turn the following below, still blank fields for period 5.

Result below is with original formula you sent:

[aduguid]

Try this 

CumulativeAmount = 
VAR _start_date = DATE(2022, 10, 01)
VAR _end_date = MAX(Table1[Date Entered]) - 365
VAR _result = 
    CALCULATE(
        COUNT(Table1[ID]) + 0,
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date]) 
            && 'Calendar'[Date] >= _start_date
            && 'Calendar'[Date] < _end_date
        )
    )

RETURN
_result

[aduguid]

Try this measure. I'm assuming your column "Month Period" is coming from your calendar table.

CumulativeAmount = 
VAR _start_date = DATE(2022, 10, 01)
VAR _end_date = MAX(Table1[Date Entered]) - 365
VAR _result = 
    CALCULATE(
        COUNT(Table1[ID]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date]) 
            && 'Calendar'[Date] >= _start_date
            && 'Calendar'[Date] > _end_date
        )
    )

RETURN
_result