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 val...
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.

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
Icon for Memorable Member rankMemorable 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