cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBISDK_COD
Frequent Visitor

This year YTD, MTD & previous year YTD and MTD

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 LYCALCULATE(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
2 ACCEPTED SOLUTIONS

Hi @PowerBISDK_COD 

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 () ) )
    )
)

 

Capture4.JPG
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.

 

View solution in original post

v-juanli-msft
Community Support
Community Support

Hi @PowerBISDK_COD 

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

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @PowerBISDK_COD 

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

View solution in original post

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
Community Support
Community Support

Hi @PowerBISDK_COD 

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.

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

Hi @PowerBISDK_COD 

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 () ) )
    )
)

 

Capture4.JPG
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.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors