topic Re: Last no blank per category in DAX Commands and Tips

Last no blank per category

Anonymous — Fri, 09 Jun 2023 12:24:27 GMT

Hola, community.

I have a fact table which brings values per category and inconsistently. Meaning some days are missing and those missing days differ per category. I want to fill those gaps with the dates for each category and give them last no blank value (corresponding to the category). I would prefer this doing in PQ. but DAX is also appreciated ( calculated column).

I checked a nioce solution here but it does not seem to mind the category so was struggling to apply that.

The current situation:

Desired solution:

Re: Last no blank per category

tamerj1 — Fri, 09 Jun 2023 13:50:07 GMT

Hi Anonymous
Please refer to attached sample file with the proposed solution

Value Measure = VAR CurrentDate = MAX ( 'Date'[Date] ) VAR CurrentCategoryTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Category] ) ) VAR TableBefore = FILTER ( CurrentCategoryTable, 'Table'[Date] < CurrentDate ) VAR PreviousRecord = TOPN ( 1, TableBefore, 'Table'[Date] ) VAR Result = SUMX ( PreviousRecord, 'Table'[Value] ) RETURN IF ( ISINSCOPE ( 'Table'[Category] ), COALESCE ( SUM ( 'Table'[Value] ), Result ) )

Re: Last no blank per category

Anonymous — Fri, 09 Jun 2023 14:05:42 GMT

Thank you for quick reply, tamerj1 !

But I would prefer a calculated column cause I need a row context. I mentioned it as well 🙂

Re: Last no blank per category

tamerj1 — Fri, 09 Jun 2023 14:17:55 GMT

Anonymous

A calculated column cannot add new rows. Perhaps you mean a new calculated table?

Re: Last no blank per category

Anonymous — Fri, 09 Jun 2023 14:48:08 GMT

tamerj1 , indeed, CC will not expand table, apologies for confusion. So yes, either calculated table either power query.

Re: Last no blank per category

tamerj1 — Fri, 09 Jun 2023 15:21:39 GMT

Anonymous
Please refer to updated sample file with the calculated table proposed solution

Table 2 = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ), ALLNOBLANKROW ( 'Table'[Category] ) ), "Value", MAXX ( TOPN ( 1, FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) && 'Table'[Date] <= EARLIER ( [Date] ) ), 'Table'[Date] ), 'Table'[Value] ) )