topic Re: Missing values in cumulative dax in DAX Commands and Tips

Missing values in cumulative dax

josuesanchezSA — Wed, 03 Jul 2024 18:36:54 GMT

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

Re: Missing values in cumulative dax

aduguid — Thu, 04 Jul 2024 05:37:21 GMT

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

Re: Missing values in cumulative dax

josuesanchezSA — Fri, 05 Jul 2024 13:44:39 GMT

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:

Re: Missing values in cumulative dax

aduguid — Fri, 05 Jul 2024 13:58:10 GMT

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

Re: Missing values in cumulative dax

josuesanchezSA — Fri, 05 Jul 2024 14:37:41 GMT

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

Re: Missing values in cumulative dax

Anonymous — Tue, 06 Aug 2024 06:50:24 GMT

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.