YTD and Previous YTD - Fiscal Year

[Gingerjeans88]

Hi all,

After a bit of help getting Year to Date calculations. 

Essentially, my fiscal year starts on a different day every year (Monday of the third week of September, so a different date every year!) so I have captured this in a calculated column using 'switch' (if sales date is greater than or equal X date and less than or equal to Y date, set this as the year'. This is obviously very manual so I have done it for many years in advance.

Somehow, I need to show YTD COUNT (not sum) of sales for the current year, as well as the previous year and the one before that. 

How do I do this and can I do it without needing to build a full calendar table? Usually I would use DATESYTD and then for the previous year just use ' - 1' year....but as the end date for each year is different this is not reliable. 

Thanks so much!

[Jihwan_Kim · Accepted solution]

Hi, @Gingerjeans88 

Please correct me if I wrongly understood your question.

In my opinion, it is better to have Dim-Caledar table based on your Fiscal Year.

I created a Dim Calendar Table in the sample pbix file (link down below), that starts from every year September 3rd week Monday. Once it is created, I think it is not too difficult to continue the calculation.

Please check the below picture and the sample pbix file's link down below.

I created some sample calculation for qty total, qty total YTD, and qty total YTD previous FY.

If you can share your sample pbix file's link, then I can try to look into in to come up with the sales count measures.

https://www.dropbox.com/s/087akcnkww08a9d/gingerjeans.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[v-deddai1-msft · Accepted solution]

Hi @Gingerjeans88 ,

You really need to build a calendar table:

Dim_Date =
VAR A =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2021, 12, 31 ) ),
        "AYEAR", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "weekday", WEEKDAY ( [Date], 2 ),
        "WEEKNUMOFMONTH",
            WEEKNUM ( [Date] ) - WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ) ) + 1
    )
VAR B =
    ADDCOLUMNS (
        A,
        "ISFirstday of a fiscal year",
            IF ( [Month] = 9 && [weekday] = 1 && [weeknumofmonth] = 3, 1, 0 )
    )
RETURN
    ADDCOLUMNS (
        B,
        "FiscalYear",
            SUMX (
                FILTER ( B, [Date] <= EARLIER ( [Date] ) && [AYEAR] = EARLIER ( [AYEAR] ) ),
                [ISFirstday of a fiscal year]
            ) + [AYEAR] - 1
    )

Then you can use the following measure:

YTD = CALCULATE(COUNT(Order[id]),FILTER(Dim_Date,Dim_Date[FiscalYear] = MAX(Dim_Date[FiscalYear])&&Dim_Date[Date]<=Today()))

LYTD = CALCULATE(COUNT(Order[id]),FILTER(Dim_Date,Dim_Date[FiscalYear] = MAX(Dim_Date[FiscalYear])-1&&Dim_Date[Date]<=DATE(YEAR(Today())-1,MONTH(Today()),DAY(Today()))))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Gingerjeans88]

Hey @v-deddai1-msft - bump on this. Any further pointers re: my below question?

when I try to build visuals usng the YTD and LYTD, now that I have built and related the dim-date table, they are all blank - after a closer look, it looks like there are some dates missing in the table. Is there a reason for this, is it because it's trimmed? See screenshot below. 

[Gingerjeans88]

hey @v-deddai1-msft - when I try to build visuals usng the YTD and LYTD, now that I have built and related the dim-date table, they are all blank - after a closer look, it looks like there are some dates missing in the table. Is there a reason for this, is it because it's trimmed? See screenshot below. 

[Gingerjeans88]

You have no idea how grateful I am for this!!!! Thank you so much!

[Jihwan_Kim · Accepted solution]

Hi, @Gingerjeans88 

Please correct me if I wrongly understood your question.

In my opinion, it is better to have Dim-Caledar table based on your Fiscal Year.

I created a Dim Calendar Table in the sample pbix file (link down below), that starts from every year September 3rd week Monday. Once it is created, I think it is not too difficult to continue the calculation.

Please check the below picture and the sample pbix file's link down below.

I created some sample calculation for qty total, qty total YTD, and qty total YTD previous FY.

If you can share your sample pbix file's link, then I can try to look into in to come up with the sales count measures.

https://www.dropbox.com/s/087akcnkww08a9d/gingerjeans.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Gingerjeans88]

@Jihwan_Kim this is outstanding and ended up, in conjunction with the top comment, really really helped. May I ask, though, is the Month Sort by FY column done in M? I can't access transform data because it's stored locally on  your machine, so that would be great to understand. 

[Jihwan_Kim]

Hi, @Gingerjeans88 

Thank you very much for your feedback.

Please check the link down below. It is the source file of the pbix file.

I think there are also some ways in creating columns for the purpose of sorting, but in this case, I created those in Power Query Editor.

https://www.dropbox.com/scl/fi/8qw6y6qx1avd3wau9t1m3/0426.xlsx?dl=0&rlkey=rm9cr1zm8ikrosiomnx5tun6l 

[Gingerjeans88]

@Jihwan_Kim Thank you SO much! Looking at the previous YTD, though, it looks like it's just counting everything from the previous year....I want it to only count until the same 'day' as today, but last year. So that I am comparing like with like. Does that make sense?

Could it be that I don't have a qty total per date row? I just have a date on every record. 

[amitchandak]

@Gingerjeans88 , Assume you can have date tbale, where you can code start of year , then have these columns

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Day of Year =datediff([Year Start date] , [Date],Day) +1

Then YTD till date

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

Also check

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Qtr]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Qtr])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.