topic Re: Filling gaps in income v target table in DAX Commands and Tips

Filling gaps in income v target table

jonbrooker — Thu, 09 Jun 2022 14:08:08 GMT

Hi all, I'm trying to create a table / graph showing cumulative income and cumulative target by week.

I'm drawing data from a table of financial records that includes a handy 'Week of created date' with which to group the individual records. However there are some weeks where no income was received, the consequence being that the target does not increment for those missing weeks. My problem is how do I fill the gaps?

I have tried creating a new table, 'Weeks' that generates a series of dates between the start and end of the timeseries with 7 day intervals, and joined that to the 'Week of created date'. I can then use the [Value] in my table, but the running target still does not increment with the blank weeks.

The [Running target] is in a table called 'Cumulative income':

Running target =

CALCULATE(

SUM('Cumulative income'[Weekly target]),

FILTER(

ALLSELECTED('Weeks'[Value]),

ISONORAFTER('Weeks'[Value], MAX('Weeks'[Value]), DESC)

)

Any advice on how I get the running target to increment with each week would be very gratefully received! If more information would help, just let me know.

Many thanks

Jon

Re: Filling gaps in income v target table

tamerj1 — Thu, 09 Jun 2022 14:17:28 GMT

Hi jonbrooker

not very clear. Do you want to fill the gaps with last nonblank previous value?

Re: Filling gaps in income v target table

jonbrooker — Thu, 09 Jun 2022 14:28:15 GMT

Hi tamerj1 , I want the Running target to increment every week, including in the weeks when there is no Total income.

Re: Filling gaps in income v target table

tamerj1 — Thu, 09 Jun 2022 14:29:09 GMT

By how much?

Re: Filling gaps in income v target table

jonbrooker — Thu, 09 Jun 2022 14:34:51 GMT

By the [Weekly target]. At the moment my Running target only goes up when the Running income also goes up. I don't understand why as I have tried to link it to a full list of weeks.

In the screenshot above, the Running target goes up in the first two weeks (when there was some income), but then stays the same in the next two weeks when there is none.