Forum Discussion

josuesanchezSA's avatar
josuesanchezSA
Frequent Visitor
2 years ago
Solved

Missing values in cumulative dax

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

 

 

 

Urgent
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    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

     

     

  • josuesanchezSA's avatar
    josuesanchezSA
    Frequent Visitor
    2 years ago

    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's avatar
      aduguid
      Memorable Member
      2 years ago

      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
  • josuesanchezSA's avatar
    josuesanchezSA
    Frequent Visitor
    2 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.