YTD and YTD Last Year

[admin_xlsior]

Hi guys,

Just need some advice,

Fristly I want to create 2 visualization for amount YTD and YTD Last Year : 

1. Clustered Column chart

2. Area chart

So I created this 2 measures :

Value YTD = TOTALYTD(SUM('inventory'[value]),'inventory'[DateKey])

and 

Value YTD Prior= CALCULATE([Inventory value YTD], SAMEPERIODLASTYEAR('Date'[DateKey]))

At first it was ok, here are the result:

But then I realized the area chart doesn't look correct, since the last value (March2019) will be drag over to the future month.

So I did some changes on the measure for YTD ->

Value YTD =

IF(LASTDATE('Date'[DateKey]) > TODAY(), BLANK(), TOTALYTD(SUM('inventory'[value]),'inventory'[DateKey]))

It will give blank to future dates, and it looks correct to my area chart :

but of course it will make my column chart false, since there is no Date dimension over there.

My YTD measure is blank now in column chart.

Is that mean I cannot use the same measure ? or the way I did is wrong ?

Thanks in advance,

[v-frfei-msft]

Hi @admin_xlsior ,

One sample for your reference. Please check the following steps as below.

1. Create a date table as below and create relationshp between it and the fact table.

date = CALENDAR(DATE(2018,01,01),DATE(2019,12,31))

2. To create measures as below.

Value YTD = TOTALYTD(SUM('Table'[value]),'Table'[Date])

Value YTD Prior = CALCULATE([Value YTD], SAMEPERIODLASTYEAR('Table'[Date]))

[admin_xlsior]

Thanks for replying,

Forgive me if I mistaken, but I think your step no 2 is incorrect. Is it not supposed to use your Date Dimension ?

Value YTD = TOTALYTD(SUM('Table'[value]),'Date'[Date])

Since if I tried to use the transaction table's date, it will give me the total amount per month instead accumulative.

Anyway this is the "final" which I can get the correct result :

Value YTD = TOTALYTD(SUM('Inventory'[value]),'Date'[DateKey])

Value YTD Prior = CALCULATE([Value YTD], SAMEPERIODLASTYEAR('Date'[DateKey]))

By that I get this result :

The left table is from the original data with no measures.

However, still, there are 2 question I' confuse :

1. Although my table has data up to Dec 2019, TotalYTD supposed to total up to today only, right ?

2. At the visualization, it will give me this :

As point no 1, the value starting May 2019, should be flat ( because that is the value up to today). Even so, I also wants the visual to stop displaying the value for the May 2019 onwards. (that is why in my first message, I've added if bigger than todays date, it is BLANK(). But then we have the same issue which I stated in my first message.

Thanks,

[v-frfei-msft · Accepted solution]

Hi @admin_xlsior ,

Please Update the two measures as below.

Value YTD = 
IF (
    ISBLANK ( [Value YTD Prior] ),
    BLANK (),
    TOTALYTD ( SUM ( 'Table'[value] ), 'Table'[Date] )
)

Value YTD Prior = 
VAR Yearmonth =
    YEAR ( MAX ( 'Table'[Date] ) ) * 100
        + MONTH ( MAX ( 'Table'[Date] ) )
VAR todayym =
    YEAR ( TODAY () ) * 100
        + MONTH ( TODAY () )
RETURN
    IF (
        Yearmonth > todayym,
        BLANK (),
        CALCULATE (
            TOTALYTD ( SUM ( 'Table'[value] ), 'Table'[Date] ),
            SAMEPERIODLASTYEAR ( 'Table'[Date] )
        )
    )

Pbix as attached.