Same Period Last Year for Custom Fiscal

DAX DAX Calculations
1

Hi,
My company has a custom fiscal. Our Fiscal 21 begins November 1st 2021.
To calculate the 2021 year to date I’ve created the below measure.

<< YTD FY21 Billings = TOTALYTD([Total Billings], ‘Date Table’[Date],“31/10”) >>

Now I want to capture the Same period last year. But none of my measures work properly.

I’ve tried this measure thinking the SAMEPERIODLASTYEAR would work, but the issue is that it doesn’t capture the proper # of months, it gives me the FULL FY20 Billings. I thought it would recognize that the YTD FY21 Billings only has 3 months of costs, so it would give me 3 months of cost from the piror year.

Same Period Last Year= CALCULATE([YTD FY21 Billings], SAMEPERIODLASTYEAR(‘Date Table’[Date]))

The image below gives the visual, the $6.8Mil is correct for the current Fiscal, but the $27Mil is the complete spend for the prior Fiscal.

In my date table, should i be adding a new column of some sorts to run the measures against?

image

2

Hi Chad,
I am not too sure, but you can try further filtering down your measure by using “filter” and specifically mentioning the names of months.
Also please provide your pbix file, so other members can have a look at your data and suggest better solution.
Regards

3

Hello,
It would be great to have a sample of your PBIX file,
Meanwhile,
I believe the key for your requirements is basically not using any of the time intelligence “built-in” expressions, including DATEADD.

PYCustom =
IF (ISFILTERED( ‘Date Table’[FiscalYear]),
CALCULATE ([YTD FY21 Billings]),
FILTER (ALL ( ‘Date Table’),
‘Date Table’[FiscalYear] = VALUES ( ‘Date Table’[FiscalYear]) - 1
&& CONTAINS(VALUES ( ‘Date Table’[Day Number of the Year]), ‘Date Table’[Day Number of the Year], ‘Date Table’[Day Number of the Year])
)),BLANK ())

The expression above is doing the same as PARALLELPERIOD but is working for Fiscal Calendars down to the week.

If this helps your resolve it, please mark it as a solution
Best regards,
Karim,

4

Hi @chad.sharpe,

Don’t know what your Date table looks like but I presume it has a Fiscal Year attribute.

TOTALYTD FY = 
VAR MaxDate = MAX( Dates[Date] )
VAR FiscalYear = MAX( Dates[Fiscal Year] )
VAR AllSalesDates =
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Sales, Dates[Date], Dates[Fiscal Year] ),
            REMOVEFILTERS ( Sales )
        ),
        "@Sales", [Total Sales]
    )
VAR ListDates =
    FILTER( AllSalesDates,
        Dates[Date] <= MaxDate &&
        Dates[Fiscal Year] = FiscalYear
    )
VAR Result = SUMX ( ListDates, [@Sales] )
RETURN

    Result

and for previous year

TOTALYTD LFY = 
CALCULATE( [TOTALYTD FY],
    DATEADD( Dates[Date], -1, YEAR )
)

To stop dates projecting forward I use an IsAfterToday filter on Report/Page or visual level.
Here’s the M code for the Date table I use.

I hope this is helpful

1 Like
5

I still couldn’t get it working… ugh… my original PBF is very large with allot of data i could not share. So I’ve taken the time to strip everything out. Which i should have done in the first place… :slightly_smiling_face:

TimeSheet - EDNA.pbix (4.6 MB)

I’ve also uploaded your new Date Table which is Great!!!

  • I’m trying to a Measure for Prior Fiscal year same period as the current year YTD
  • as well the area chart has a trailing period from Feb onward because i have no data for those dates yet, how do i remove those dates from the visual
6

Hi @chad.sharpe,

Incorporated the measures and placed a filter on the Report page.

image

TimeSheet - EDNA.pbix (4.6 MB)
I hope this is helpful

1 Like
7

Perfect!!!
The “IsAfterToday” filter is also great…

8

Hi @chad.sharpe, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!