YTD Measure not working properly

[bchandl7]

Hello,

I am very new to Microsoft BI, and I have a problem I am trying to solve. I have a measure that I have created to calulate sales amounts based off a fiscal year column:

FY15 Sales Amt = CALCULATE(
SUM('Sales'[Sales Amt]),
FILTER( 'Sales','Sales'[Fiscal_Year] = (Max('Sales'[Fiscal_Year])-2)))

This is taking the Max fiscal year which is currently 2017 and subtracting 2 to show 2015 numbers. So far, this works except for one wrinkle. Our current fiscal year is at 5/26/2017 and ends on 6/30. I would like to see the previous years as ytd numbers to compare apples to apples.

I have tried:

FYTD15 Sales = TOTALYTD(SUM('Sales'[Sales Amt]),dCalendar[Date], FILTER( 'Sales','Sales'[Fiscal_Year] = (Max('Sales'[Fiscal_Year])-2)), "6/30")

When I use this, nothing is returned but blanks. Also, dCalendar[Date] in the expression is using a created date table. 

Any help with this would be appreciated!

Ben

[bchandl7]

It looks like the following has gotten it down to the correct fiscal year month.

First I created a Fiscal Month expression:

FiscalMonth = (If( Month('Sales'[date]) >= 7  , Month('Sales'[date]) - 6,Month('Sales'[date]) + 6 ))

Then I created a max fiscal year month expression:

Max_FY_Month =
CALCULATE(
MAX('Sales'[FiscalMonth]),
FILTER(ALL('Sales'), 'Sales'[Fiscal Year]=MAX('Sales'[Fiscal Year])
))

Finally I updated the FY15 Sales Amt expression as follows:

FY15 Sales Amt = CALCULATE(
SUM('Sales'[Sales Amt]),
FILTER( 'Sales','Sales'[Fiscal_Year] = (Max(Sales'[Fiscal_Year])-2) && 'Sales'[FiscalMonth] <= 'Sales'[Max_FY_Month] ))

This is pulling the as of the end of the month for fy 15. I would suspect I could add one more filter for the 'fiscal year day' but I'm not sure how to create a number. My fiscal year goes from 7/1 - 6/30.

[Anonymous]

It's totally crazy I didn't mention this, but you should certainly create a date table, and create a relationship between new Calendar[Date] and your Sales[Date] column.

If you end up using built in time intelligence functions, it will likely be required because they don't allow "gaps" in your date range (eg, no sales on a sunday, <crash>).   If you are doing something more custom (say, a 4,4,5 calendar), you will need it to build "helper columns" of useful stuff... say FiscalMonthId

In the measure you wrote below... (which should be written against a new calendar table), you are still missing ALL( ).

FILTER( ALL('Sales'),'Sales'[Fiscal_Year] = (Max(Sales'[Fiscal_Year])-2) && 'Sales'[FiscalMonth] <= 'Sales'[Max_FY_Month] ))

[bchandl7]

I thought you might catch that. I actually created a calendar, but when I use it, the totals aren't exactly correct for some reason. 

Here is my calendar table expression:

dCalendar = CALENDAR(MIN('Sales'[date]),MAX('Sales'[date]))

I created a one to many relationship from the calendar to sales. Totals/sums are not exactly right. However when I created date columns within the sales table the totals/sums are correct.

This has been bugging me because I'd rather have one date calendar. Any ideas how I might update the calendar calculation to use it properly?

Also, thanks for pointing out the 'ALL' piece of the filter.

Thanks!

[Anonymous]

You are gonna have to give me more to go on... 🙂   How are the numbers off?

[v-ljerr-msft]

Hi @bchandl7,

Have you tried the formula below to see if it works? 

FYTD15 Sales =
TOTALYTD (
    SUM ( 'Sales'[Sales Amt] ),
    dCalendar[Date],
    FILTER (
        ALL ( 'Sales' ),
        'Sales'[Fiscal_Year]
            = ( MAX ( 'Sales'[Fiscal_Year] ) - 2 )
    ),
    "6/30"
)

Regards

[Anonymous]

A few things here:

* Your FY15 Sales Amt measures is... probably slightly misnamed, since next year... it is suddenly going to show FY16.

* If you really want 2015, (as opposed to "two years ago"), you can just use:

    FY15 Sales Amt = CALCULATE(Sales[Sales Amt], Sales[Fiscal_Year] = 2015)
  but it's a bit of an odd use case.  Typically I would just have Total Sales, and it sums all the sale amt, end of story.  (Cuz if I put that measure w/ Year on rows... it works just fine in that context)

* For your YTD I would again try using the simplified Sales[Fiscal_Year] = 2015 if that's your think, but otherwise... likely the problem is that you need to use FILTER(ALL(Sales) ... instead of just FILTER(Sales, ...
   Otherwise when you context is FY16 it is saying "show me all the FY14 in FY16" and there aren't any.  the ALL( ) will nuyke the filter on FY16 and then let it be reset to FY14

[bchandl7]

Thank you for the great feedback. My main goal is to always dynamically calculate the current and previous fiscal ytd totals without needing to change the formula. I have now tried using ALL filter on 'Sales'. I am not getting any errors with this measure, but it is still showing blank values.