Fiscal Date Calendar Calculations with Date Add

[cgoldstein]

 Hello all!

Please pardon my questions as I am just beginning with DAX and haven't been able to really understand some solutions working through fiscal calendar that I've found. 

I've loaded in my date table from my company's database which includes calendar day as well as all fiscal attributes. Is there a way to switch over the time functions from calendar to fiscal? (I would like to use SAMEPERIODLASTYEAR functions etc.)

If not, I've tried using the "Date Add" function, though have not had any success. I'm not sure if this is due to how I've set up the fiscal date table or a different calculation is needed when using fiscal.

Date table below:

[MarkLaf]

As long as your visual is using fiscal labels (e.g. fiscal year for particular date instead of calendar year), which already appear to be in your Dates table, then I believe you should be able to use SAMEPERIODLASTYEAR as the filter argument in CALCULATE to get what you want as described in the function reference.

Or did you try that and are getting an error or unexpected output? Please provide additional details if that is the case.

[cgoldstein]

The functIon is working without error, however the numbers it is returning are not correct.

I checked the data set to ensure my date table was properly loaded and validated by splicing it by year, everything aggregates correctly. However, when I use the function with same period last year, the results are incorrect.

My company runs off of a 4-4-5 fiscal calendar, where the start of the year falls on 12/29/2017. I've loaded "calendar date" as the date to reference, however all other information in the date table is fiscal (fiscal_yr_nr, fiscal_mn_nr). It appears that using this function is maybe reading the calendar year potentially because even the totals for the year do not match.

Total Units LY = CALCULATE([Total Units],SAMEPERIODLASTYEAR('Date'[Date]))

Any help would be greatly appreciated, as I'm at a loss for how to correct this!

[cgoldstein]

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

I found this link and think I may have to do something similiar, I'm still not sure whether I would be able to do this with my current calendar. I don't have any import from our database date table in terms of which days have actualized so far as its using the fiscal day number in these calcs.

[v-chuncz-msft]

@cgoldstein,

For any time intelligence function, you could implement a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/