YTD Prior Year based on Fiscal Calendar YYYYWW

[AmberM]

I am struggling with creating the formula for YTD prior year that cumulates based on current YTD.

My Current YTD Sales formula is working:

YTD Sales = 
[Total Sales](
                FILTER(
                  ALL(FYCalendar),
                  FYCalendar[FY Week Nbr]<=MAX(FYCalendar[FY Week Nbr])
                ),
                VALUES(FYCalendar[FY])
             )

When I try to create the formula for Prior YTD Sales, I receive an error:

YTD Sales Prior Year = 
    [Total Sales],
      FILTER(ALL(FYCalendar),
          FYCalendar[FY Week Nbr]=MAX(FYCalendar[FY Week Nbr]) - 1),
          VALUES(FYCalendar[FY])
)

How do I fix the prior year expression for YTD based only on time accumulated for the current YTD?

Thanks so much if you can assist!

[v-xjiin-msft]

Hi @AmberM,

Could you please share us more information about your report if possible? Like your source table structure and some sample data with corresponding expected result. So that we can know the right direction and make some proper tests. 

Thanks,
Xi Jin.

[AmberM]

Here is a sample file on OneDrive with a few iterations of trying to create YTD Sales that accumulate for the prior year. Please let me know if you have issues accessing the file, and thank you for your assistance!

https://1drv.ms/u/s!AoY5sA-v6cUciH3yi2ZGonBagbbG

[Ashish_Mathur]

Hi,

In the Sales Table, you do not have a date column - only a Week column.  In the source from where you are downloaidng, do you have a Date column?  If not a date colun, even if you have month and year in 2 seperate columns, we can always generate a date.

Having a date column will allow us to use DAX's Date and Time Intelligence functions.

[AmberM]

Unfortunately that is my issue. The data only returns with a fiscal YYYYWW which does not match a calendar year. I am not able to use DAX calculations to create the prior year expressions.