Showing a correct YTD by month if 1 month has no action

[rpinxt]

I have this small example data table:

PostingDate	EoM	Material	Quantity
12-01-21	31-01-21	abc	50
23-01-21	31-01-21	abc	75
08-02-21	28-02-21	abc	25
25-02-21	28-02-21	abc	100
01-04-21	30-04-21	abc	200
29-04-21	30-04-21	abc	65
15-01-22	31-01-22	abc	150
17-01-22	31-01-22	abc	35
10-02-22	28-02-22	abc	85
15-02-22	28-02-22	abc	115
03-04-22	30-04-22	abc	155
19-04-22	30-04-22	abc	95
05-03-21	31-03-21	def	10
05-03-22	31-03-22	def	35

Now I created a YTD measure and want to show it over time by period (for now I put it in a matrix):

So the problem here is that period 3 shows no data for material abc.

All the numbers are fine and also the total is fine but I would like to have period 3 populate despite that there was no action.

What period 3 should show is the same ending balance as period 2.

So that when you would put things in a chart there would not be a broken line or en empty bar/column.

Guess if it is possible it would need to be done by DAX.

So how would I tell it to show the same YTD end balance as previous month if nothing happend in the "current" month?

This is my YTD measure now:

YTD Qty =

VAR MaxDate = MAX(Sheet1[EoM])

VAR Result =

    CALCULATE(

        SUM(Sheet1[Quantity]),

        Sheet1[EoM] <= MaxDate,

        ALL(Dim_Date)

    )

RETURN

    Result

[Dinesh_Suranga · Accepted solution]

@rpinxt 

Try this.

Total = CALCULATE(SUM('Table'[Quantity]), FILTER(ALLEXCEPT(Calender,'Calender'[Date].[Year]), 'Calender'[Date] <= MAX('Calender'[Date])))

[rpinxt]

Hi @Dinesh_Suranga ,

Nope tried that. That does not work :

Just adds a lot of extra empty columns.

And you cannot use the "select items with no data" on a measure.

[Dinesh_Suranga]

@rpinxt 

Hi,

Do you use a seperate calender table?

Thank you

[rpinxt]

Yes I do.

[Dinesh_Suranga]

@rpinxt 

Try following DAX.

YTD = TOTALYTD(SUM('Table'[Quantity]), Calender[Date])

[rpinxt]

Yes I was using now the TOTALYTD function:

Test = TOTALYTD(SUM(Sheet1[Quantity]),Dim_Date[Date])

However as you see it resets at the year.

I want it to continue after 2021. It should not reset on Year.

[Dinesh_Suranga · Accepted solution]

@rpinxt 

Try this.

Total = CALCULATE(SUM('Table'[Quantity]), FILTER(ALLEXCEPT(Calender,'Calender'[Date].[Year]), 'Calender'[Date] <= MAX('Calender'[Date])))

[rpinxt · Accepted solution]

Yes I think I again was using something similar which seems to work the same:

Test2 = CALCULATE(SUM(Sheet1[Quantity]),FILTER(ALLSELECTED(Dim_Date),Dim_Date[Date] <= MAX(Dim_Date[Date])))

Thanks for helping me along 🙂

[Dinesh_Suranga]

@rpinxt 

Hi,

Tick

Select items with no data.

Thank you.