Fiscal Calendar Measure By Day Last Year

DAX DAX Calculations
1

It’s my first time working with fiscal calendar and I’ve already learned a lot from Enterprise DNA!

In this case, I am trying to get the data from last year day by day.
Right now I am getting the entire period like the dark blue line below. This visual is controlled by a fiscal month slicer.
image

Here is a sample .pbix. Can you please point me in the right direction?
Fiscal Year Testing.pbix (738.6 KB)

2

What if you try something like this?

Total Sales LFY = 
CALCULATE(
    [TotalSales],
    DATEADD(Dates[Date], -1, YEAR)
)

Does that give the result you are looking for? If you simply want the data from one year earlier on each date, fiscal year doesn’t make any difference. You just need the Sales amount from the selected date minus one year.

3

This is not directly related to your question, but it appears that the [Sales] value for each date represents YTD total sales instead of the actual sales total for that specific day. Is there a reason you have your data set up this way? It would seem that not having individual daily totals would limit the types of analysis you can do on your data.

4

Hi Dave,
Thanks for your response. In this case, the users are looking for data from the same fiscal day last year, which is not equal to date -1 year. For example, Day 1 of fiscal year 2019 is 12/30/2018 , while Day 1 of FY 2020 is 12/29/2019 and FY 2021 starts on 12/27/2020. I guess I could get the difference between the dates and subtract it so that the comparison is -1 year + or - the offset. I think that will work! Will let you know how it goes.

5

Yes, previously this report was set up to do Fiscal YTD and Fiscal MTD analysis. Now we are looking at the individual days.

6

OK, I went back and looked more closely at the logic you were trying to apply in your [Total Sales Last Fiscal Year] measure. You had the right idea, but the FILTER() function was returning an entire month of dates, and the measure was summing the Sales numbers for every day in the entire month.

Here’s a revised version of your measure that should give you what you wanted…

Total Sales Last Fiscal Year = 
VAR _selectedFiscalDay = SELECTEDVALUE(Dates[Fiscal Day of Year],0)
VAR _selectedFiscalYear= SELECTEDVALUE(Dates[Fiscal Year Only],0) -1
RETURN
CALCULATE(
    [TotalSales],
    FILTER(
        ALL(Dates), 
        Dates[Fiscal Day of Year] = _selectedFiscalDay && Dates[Fiscal Year Only] = _selectedFiscalYear
    )
)
1 Like
7

Hi @DeanJ, 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!

8

Thank you!!