cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reuben Regular Visitor
Regular Visitor

YTD vs LYTD vs Full Year n-1 vs Full Year n-2

 

Hello,

 

I am trying to compare YTD (1) production at the selected Financial Year and Financial month filtered with a the following slicer

(Slicer Year = FY19 / Slicer Month = March) with last year in the same month LYTD (2).

(1) YTD  Production = calculate([Production];datesytd('Calendar'[Date];"30/06") -

(2) LYTD = calculate([YTD Production];SAMEPERIODLASTYEAR('Calendar'[Date]))

Then I want to compare also with last full year (FY18, from 01/07/17 to 30/06/18) and FY17 (from 01/07/16 to 30/06/17). How could I do this?

 

Thank you very much for your help

Reubben

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

You could write a custom time intelligence functions to do this

Sales Current Year =
VAR CurrentYear =
    MAX ( calendar[year] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( calendar ), calendar[Year] = CurrentYear )
    )

and this

Sales Last Year =
VAR CurrentYear =
    MAX ( calendar[year] )
VAR LastYear = CurrentYear - 1
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( calendar ), calendar[Year] = LastYear)
    )

You can read my article about time intelligence in Power BI here 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Reuben Regular Visitor
Regular Visitor

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

Hi @MattAllington 

Thank you very much for answering. Following your tip I have applied your measure to my model and it gives me a wrong result as I am working with fiscal year ( from 1s july to 30th june). Following the logical, if I change Calendar[Year] for Calendar[FiscalYear], the result is an error. May be is because Calendar[FiscalYear] in the calendar table is difined as FY16, FY17... FY20, but I don´t know how to solve it.

 

By the way, I have take a look to your article and I find it very interesting. Its very helpfull for  newbies like me.

 

Production Last Year = 

VAR CurrentYear =

    MAX ( calendar[FiscalYear] )

VAR LastYear = CurrentYear - 1

RETURN

    CALCULATE (

        [Produccion];

        FILTER ( ALL ( 'Calendar' ); 'Calendar'[FiscalYear] = LastYear)

    )

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

You could write a custom time intelligence functions to do this

Sales Current Year =
VAR CurrentYear =
    MAX ( calendar[year] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( calendar ), calendar[Year] = CurrentYear )
    )

and this

Sales Last Year =
VAR CurrentYear =
    MAX ( calendar[year] )
VAR LastYear = CurrentYear - 1
RETURN
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( calendar ), calendar[Year] = LastYear)
    )

You can read my article about time intelligence in Power BI here 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Reuben Regular Visitor
Regular Visitor

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

Hi @MattAllington 

Thank you very much for answering. Following your tip I have applied your measure to my model and it gives me a wrong result as I am working with fiscal year ( from 1s july to 30th june). Following the logical, if I change Calendar[Year] for Calendar[FiscalYear], the result is an error. May be is because Calendar[FiscalYear] in the calendar table is difined as FY16, FY17... FY20, but I don´t know how to solve it.

 

By the way, I have take a look to your article and I find it very interesting. Its very helpfull for  newbies like me.

 

Production Last Year = 

VAR CurrentYear =

    MAX ( calendar[FiscalYear] )

VAR LastYear = CurrentYear - 1

RETURN

    CALCULATE (

        [Produccion];

        FILTER ( ALL ( 'Calendar' ); 'Calendar'[FiscalYear] = LastYear)

    )

View solution in original post

Super User
Super User

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

You need an integer FY column. Just add one to your calendar as a calc column

 

FY = right(calendar[fiscalyear],2)



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Reuben Regular Visitor
Regular Visitor

Re: YTD vs LYTD vs Full Year n-1 vs Full Year n-2

Thank you @MattAllington  it works!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 142 members 1,698 guests
Please welcome our newest community members: