YTD Calculation without Date Field

[Rustin788]

Hey,

I'm trying to figure out the best way to have YTD calculations in data that is using a fiscal calendar that puts days in different months. My dataset (SalesData) has a Fiscal-Month field as well as Fiscal_Year field. I want the final table to include the Customer, ItemNo, Totals for each YTD, and then the Change and Change %. Everything I have found seems to rely on using a date field.

Appreciate any help you can provide.

[gmsamborn]

Hi @Rustin788 

Because your granularity is by the month, if you use the Power Query column at the start, you might be able to ignore the Date table for now but you will need a lot more measures with a hard-coded start to your fiscal year.  (I hate going back to make "annual" changes.)

With an odd fiscal year like that, you will eventually need a custom date table like that.  It is always best practice to have a date table even if it is an unusual fiscal year.   

A lot of time intelligence functions like DATESYTD may or may not  work for you even with a date table.

If the granularity ever changes from by the month, you'll probably have a LOT more work because of the unusual fiscal year.

[Rustin788]

The "good" thing is in about a year when all this data finally goes into a single ERP system, I'm going to have to rebuild everything anyway and that is when we might be able to start updating the data more regularly.  Until then, I receive a monthly data dump and that is it.

So I guess what would be the easiest way for me to compare 2022 and 2023 while just using the Fiscal Month field as the filter?

I am currently using this:

Difference FY23 vs FY22 = CALCULATE( SUM(Sales[Qty]), Sales[FiscalYear] = 2023 ) - CALCULATE( SUM(Sales[Qty]), Sales[FiscalYear] = 2022 )

However, I don't know how to get a visual to Show 2022 Sales, 2023 Sales, and then the difference.  It want show the difference under each year column.

[gmsamborn]

Hi @Rustin788 

I created the below Calendar table and a column to add to your Sales table (in Power Query).  This is for a date field to be used in a relationship with the Calendar table.

Power Query - Add this column to your Sales table.

= Text.From([FISCAL_YEAR]) &
"-" &
[FISCAL_MONTH] &
"-15"

DAX Calendar table

/* Table */
Calendar = CALENDAR( DATE( 2022, 1, 1 ), DATE( 2024, 12, 31)

/* Calculated Columns */
Year = YEAR( [Date] )

Week = 
VAR _Start =
    CALCULATE(
        MIN( 'Calendar'[Date] ),
        ALL( 'Calendar' )
    )
RETURN
    INT( ( [Date] - _Start ) / 7 ) + 1

Week of Year = MOD( [Week], 52.0001 )

Quarter = INT( [Week of Year] / 13.0001 ) + 1

Week of Quarter = MOD( [Week of Year], 13.0001 )

Period of Quarter = 
    SWITCH(
        TRUE(),
        [Week of Quarter] <= 5, 1,
        [Week of Quarter] <= 9, 2,
        3
    )

Period No = 
    SWITCH(
        TRUE(),
        [Quarter] = 1 && [Period of Quarter] = 1, 1,
        [Quarter] = 1 && [Period of Quarter] = 2, 2,
        [Quarter] = 1 && [Period of Quarter] = 3, 3,
        [Quarter] = 2 && [Period of Quarter] = 1, 4,
        [Quarter] = 2 && [Period of Quarter] = 2, 5,
        [Quarter] = 2 && [Period of Quarter] = 3, 6,
        [Quarter] = 3 && [Period of Quarter] = 1, 7,
        [Quarter] = 3 && [Period of Quarter] = 2, 8,
        [Quarter] = 3 && [Period of Quarter] = 3, 9,
        [Quarter] = 4 && [Period of Quarter] = 1, 10,
        [Quarter] = 4 && [Period of Quarter] = 2, 11,
        [Quarter] = 4 && [Period of Quarter] = 3, 12,
        0
    )

Period = 
    SWITCH(
        [Period No],
        1, "JAN",
        2, "FEB",
        3, "MAR",
        4, "APR",
        5, "MAY",
        6, "JUN",
        7, "JUL",
        8, "AUG",
        9, "SEP",
        10, "OCT",
        11, "NOV",
        12, "DEC",
        "Error"
    )

Fiscal Year = 
    IF(
        MONTH( [Date] ) = 12 && [Period] = "JAN",
        YEAR( [Date] ) + 1,
        YEAR( [Date] )
    )

YTD Sales = 
VAR _StartofYear =
    CALCULATE(
        MIN( 'Calendar'[Date] ),
        ALL( 'Calendar'[Date] ),
        'Calendar'[Fiscal Year] = MAX( 'Calendar'[Fiscal Year] )
    )
VAR _YTD =
    CALCULATE(
        [Sales],
        'Calendar'[Date] >= _StartofYear
            && 'Calendar'[Date] <= SELECTEDVALUE( 'Calendar'[Date] )
    )
RETURN
    _YTD

5-4-4 calendar.pbix

[Rustin788]

Hey, I get a big error message that starts with "The syntax for 'Year' is incorrect.".  Would it be easier if I didn't need a true YTD calendar but rather something that would go through the previous period?  So even if I am running it on 12/20, I just need the YTD data to be Jan-November.

[gmsamborn]

Which calculated column were you creating when you got the error message?

[gmsamborn]

Hi @Rustin788 

Can you describe your "fiscal calendar that puts days in different months" ?

Can you share your date table?

[Rustin788]

Hey,

It is a 5,4,4 fiscal calendar, so January has 5 full weeks, Feb has 4 full weeks, etc. The first day of 2024 is actually 1/31/23 in what we are using.

At the moment, someone else is adding that in before I get the data so I have a text field fical month & year. (We aren't allowed ODBC to get this data)  However, I do have the start of a calendar in PowerBI that I am hoping to use.

Here is a small fake sample of the data as well as the calendar table I'm setting up.

https://docs.google.com/spreadsheets/d/1W0p73pIW-ppt1pFlRv5K6nI1Ll_kS1QB-4fejOkalB0/edit?usp=sharing

[gmsamborn]

Hi @Rustin788 

Does this match your Fiscal calendar?

My Calendar Table.xlsx

I just want to make sure before I start writing the DAX script.  (Maybe in Power Query later.)

[Rustin788]

Yes, that appears to be it.

[PijushRoy]

Hi @Rustin788 

You can use one trick here
You have the Year and month, you can create a Calculated column for the Date
The date will be the Start date or end date of the month example if the month is Jan show date should be 1.1.Year or 31.1.Year
Then you can use the date column for YTD calculation
If this trick works for you, keep posted