This year YTD, MTD & previous year YTD and MTD

[Anonymous]

Dear Forum Members,

I am trying to create a year to date comparison using Power BI, I have used the below:

Total Views =Count('Views'[ReferenceId])

Total Views TY =TOTALYTD([Total Views],DATESYTD(Dates[Date])) //Current Year total views

Total Views LY = CALCULATE(Count('Views'[ReferenceId]),PREVIOUSYEAR(Dates[Date]))  - The reasone for this - as I want the total for the Year.

Total LY YTD = CALCULATE(COUNT('Views'[ReferenceId]),SAMEPERIODLASTYEAR(Dates[Date])) 

Total LY YTD is the one which is giving me a trouble, it does bring me the total until the end of the month since the beginning of the Year, for example we are in November and Today is 9th november 2019, I wanted to go back until this date means 09/11/2018 but for some reason it brings me back the whole month and similar for MTD as well, my previous year MTD calculation below:

Current Period LY = CALCULATE([Total LY YTD],DATESMTD(Dates[Date]))

I have tried several scenarios but still not getting to the bottom of it. I have created a calendar table based on the below formula:

Dates = CALENDAR(min('Views'[View_CreatedDate]),max('Views'[View_CreatedDate]))

 Please kindly help, any help would be appreciated.

Kind regards

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I create a date table, 

date table = CALENDARAUTO()

Then create measures

this year ytd = TOTALYTD(SUM(Sheet1[value]),'date table'[Date],'date table'[Date]<=TODAY(),"12/31")

or if you only have data till today for thsi year, you can use the measure below directly,

this year ytd 2 = CALCULATE(SUM(Sheet1[value]),DATESYTD('date table'[Date],"12/31"))

Then you can create another measure ofr last year ytd,

last year ytd =
CALCULATE (
    [this year ytd 2],
    SAMEPERIODLASTYEAR ( 'date table'[Date] ),
    FILTER (
        ALL ( 'date table' ),
        'date table'[Date]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Dear @v-juanli-msft 

Thank You very much for Your response, accept my sincere apologies for the delayed reply, I was working on some other projects and for some reason Power BI community response gone to my junk mail.

I have tried Your logic within my Dashboard and it does not seem to work but the solution You gave me its quite promising, probably data modelling issue on my side. What does not make sense if it is a data modelling issue then as soon as I go down the lowest level of date measure which is a single date then it works absolutely like a charm i.e. if I select the most recent date from the slicer - just a single date.

I will accept this answer as Yes as the working example/solution You have provided its an effort and it seems that this is the way forward for my issue, it is clearly working in the solution provided.

Many thanks for Your response, appreciated.

[v-juanli-msft]

Hi @Anonymous 

Do you mean the Total LY YTD shows the values from 2018/11/1 to 2018/11/9,

but what you want is the values from 2018/1/1 to 2018/11/9?

Please check the min date in your "Dates" table,

if the min date is 2018/11/1, then the Total LY YTD would not return more data before 2018/11/1,

If in this case, please create the date table as below

Date=CALENDARAUTO('Views'[View_CreatedDate])

If not , please show me when the problem occurs, 

It is better to show some screenshots or data example/files.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear @v-juanli-msft 

First of all Thank You very much for Your response, appreciated.

I will try and explain as I have to do a sample data with a sample report which i will try and send later Today.

This year YTD means - beginning of the Year till date - for example Today is 16/11/2019 so:

This Year YTD = January 1, 2019 till November 16, 2019 (result 1)

Last Year YTD = January 1, 2018 till November 16, 2018 (result 2)

Above are the expected results i.e. result 1 and result 2 but What I am getting for Last Year is the below:

 Last Year YTD = January 1, 2018 till November 30, 2018 

Did not understand the behaviour why is this happening - I have created a calendar as per the below and took the max. date out which I have now replaced with the Today date, My data should be until 16 November 2018 for last year YTD and same for MTD but its not happening.

does this make sense?

Please kindly advise

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I create a date table, 

date table = CALENDARAUTO()

Then create measures

this year ytd = TOTALYTD(SUM(Sheet1[value]),'date table'[Date],'date table'[Date]<=TODAY(),"12/31")

or if you only have data till today for thsi year, you can use the measure below directly,

this year ytd 2 = CALCULATE(SUM(Sheet1[value]),DATESYTD('date table'[Date],"12/31"))

Then you can create another measure ofr last year ytd,

last year ytd =
CALCULATE (
    [this year ytd 2],
    SAMEPERIODLASTYEAR ( 'date table'[Date] ),
    FILTER (
        ALL ( 'date table' ),
        'date table'[Date]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.