Trying to use date table to create YTD, LY & PYTD for a company whose first fiscal month is April. Any suggestions or a date table template that has all of the fiscal breakdown to do these calculations would be much appreciated.
Thanks
Here’s all the resources you need to solve this.
Another link here also
That was a great start, but need to come up with a formula to add a column for fiscal week. I believe the correct dax I need for YTD is :
Total Sales FYTD:
CALCULATE (
[Total Sales],
FILTER (
ALL ( Calendar ),
Calendar[FinYear] = MAX ( Calendar[FinYear] )
&& Calendar[FinWeek] <= MAX ( Calendar[FinWeek] )
)
This shows you the formula of how to do this which is what you already have I think.
Try this formula in a calculated column and see if this gets you the financial week column you need.
Financial Week No. =
INT( ( Dates[Date] - ( WEEKDAY( Dates[Date] ) - 1 ) -
DATE( YEAR( Dates[Date] ) - ( MONTH( Dates[Date] ) < 4 ), 4, 1 ) ) /
7 ) + 2Thanks. I got the financial day of year & week No. figured out, but YTD is still not working? If I try and put the measure for YTD in a table it shows blank, but if I use a filter using fiscal year, the # shows up correct. 
Here is copy where measure showing up correctly due to slicer of fiscal year.
Just make sure this is ticked
It’s attempting to count up the column in your image which you don’t want
Went through everything in the calendar table, and all of the items in it have Don’t summarized ticked. Thanks again for all the help. It is still not calculating correctly though? Here is copy of my measure
Your missing the ALL( DatesTable ) on this one after the FILTER function
On the second one DATESYTD won’t work with weekly calendar. What results is this one returning?
Ok it has to be your relationship then.
What does the model look like and what are your relationships. This would always be the first thing to check when getting no results actually.
I think the other reason is that you have no context on the calculation in the table? Why is that?
Are you just looking for the total and that’s it? This could be the problem actually.
Just looking to get the total amount of job start dates for YTD vs PYTD. I have a calculation that works for PYTD.
Try this formula…it should work by my testing
Total Sales YTD - Custom 2 =
VAR CurrentFinWeek = MAX( 'Calendar Daily'[Fin Week Number] )
VAR CurrentFinYear = MAX( 'Calendar Daily'[Fin Year] )
RETURN
SUMX(
FILTER(
SUMMARIZE( ALL( 'Calendar Daily' ), 'Calendar Daily'[Fin Year], 'Calendar Daily'[Fin Week Number],
"Sales", [Total Sales] ) ,
'Calendar Daily'[Fin Year] = CurrentFinYear &&
'Calendar Daily'[Fin Week Number] <= CurrentFinWeek ),
[Sales] )
I think the SELECTEDVALUE was the issue on the total.
Here is what I get with new formula. Not getting any totals at bottom though? Getting correct # of Start Date though.
. Still showing up blank if I just put it in a table all by itself or in a card?
I have the calendar table going all the way out to the year 2090. Is it possible with this calculation have to put something in about last non blank?
Here is copy of slide I want this # to appear on. I have not done this calculation for any of the other #'s you see, they are calculating correctly on a traditional YTD & PYTD calculation. The Start Date Total PYTD is correct though, but you can see that neither one of the measures show anything.
I figured it out. I created a measure for today’s fiscal day of the year=
Todays Fiscal Day Of Year
= CALCULATE(MAX('Calendar'[Fiscal Day]),'Calendar'[Date]=TODAY())
Then created calculated column call Fiscal YTD=
Fiscal YTD = IF(YEAR(TODAY())=[Fiscal Year]&&[Fiscal Day]<=[Todays Fiscal Day Of Year],TRUE,FALSE)
The created Measure for FYTD=
Count Start FYTD = CALCULATE([Count Start],'Calendar'[Fiscal YTD]=TRUE())