Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kevingraves30
Frequent Visitor

Current Fiscal YTD and Same Period LY

I am having some trouble and was hoping someone could point me in the right direction.  I am wanting to have a table that shows Total Sales by Customer and sales for same period last year.  I have a Date Table that has a column defining all dates in our current fiscal year and I have used that column as a slicer.  The problem I am having is when I choose the fiscal year slicer the total sales calculates Fiscal YTD, but the Same Period Last Year incudes the entire month of March instead of only showing to todays date.

 

Here is how I have defined my Current Fiscal Year column (our Fiscal Year begins November 1st):  Fiscal Year = If(DateTable[Date].[MonthNo] >= 11 && MONTH(NOW()) <= 10 && DateTable[Date].[Year] = YEAR(NOW()) -1, "Fiscal Year", IF(DateTable[Date].[MonthNo] <= 10 && DateTable[Date].[Year] = YEAR(NOW()), "Fiscal Year"))

 

My LY Same Period  measure is defined like this: LY Same Period = CALCULATE(SUM(Sales[Total Sales]),SAMEPERIODLASTYEAR(DateTable[Date]))

 

 

I am guessing this has something to do with the formula not defining a definative date range.  I am new to this, so any help would be appreciated.

4 REPLIES 4
v-sihou-msft
Employee
Employee

@kevingraves30

 

In this scenario, as you said, your Fiscal Year start at Nov. You should add Fiscal Year and Fiscal Month columns in your table instead of just using one column to tagging if it's in Fiscal Year.

 

I assume you have Year, Month and Day columns in your Date table. Please add calculated columns below:

 

Fiscal Year = IF('Calendar'[Month]>10,'Calendar'[Year]+1,'Calendar'[Year]) 
Fiscal Month = IF('Calendar'[Month]>=11,'Calendar'[Month]-10,'Calendar'[Month]+2)

Since we are not under a regular calendar, we need to add a calculated column for Last Fiscal Year on Day granularity.

 

 

Same Day Last Fiscal Year =
CALCULATE (
    SUM ( 'Calendar'[Amount] ),
    FILTER (
        'Calendar',
        'Calendar'[Fiscal Year]
            = ( EARLIER ( 'Calendar'[Fiscal Year] ) - 1 )
            && 'Calendar'[Fiscal Month] = EARLIER ( 'Calendar'[Fiscal Month] )
            && 'Calendar'[Day] = EARLIER ( 'Calendar'[Day] )
    )
)

61.PNG

 

 

62.PNG

 

To calculate the YTD, you can still use TOTALYTD() with start date specified.

 

Fiscal YTD =
TOTALYTD (
    SUM ( Calendar[Amount] ),
    Calendar[Date],
    ALL ( 'Calendar' ),
    "11/1"
)

 

Regards,

@v-sihou-msft

 

Thanks for the response!  The reason I was trying to define a column as Fiscal Year is because I want to have a slicer that shows fiscal year and when you choose it it shows you total sales for the fiscal year and also the sales for the same period last year. 

 

I tried using the Same Day Last Fiscal Year formula, but was unable to get it to work.  I already had a column for Fiscal Month and a column for Fiscal Year in my table called "DateTable".  All of my sales are in a table called "Sales".   Below is how I tried to create the DAX formula, but I wasn't able to use DateTable[FiscalMonth] in the "Earlier" function. 

 

I have also attached a screen shot of how the dashboard looks.  I just need to get the "Fiscal Year" and "Prev Fiscal Year", buttons doing true comparison to the day for Total Sales and LY Same Period.

 

Same Day Last Fiscal Year =
CALCULATE (
    SUM ( 'Sales'[Total Sales] ),
    FILTER (
        'DateTable',
        'DateTable'[Fiscal Year]
            = ( EARLIER ('DateTable"[FiscalYear]) - 1 )
            && 'DateTable'[FiscalMonth] = EARLIER ( 'DateTable'[FiscalMonth] )
            && 'DateTable'[DayOfYear] = EARLIER ( 'DateTable'[DayOfYear] )
    )
)

 Customer Dashboard.png

 

Any other suggestions on how I can get a Fiscal Year Slicer that shows fiscal YTD vs samperiodlastyear in my table.  I haven't been able to get this to work with the suggestion made.

 

 

kevingraves30
Frequent Visitor

Could someone please point me in the right direction with this?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.