Forum Discussion
Custom Fiscal Year error
arp2 - this will work for last week:
VAR LastWeekStart = SELECTEDVALUE ( FYCalendar[WeekStart] ) - 7 VAR LastWeekEnd = SELECTEDVALUE ( FYCalendar[WeekEnding] ) - 7 RETURN CALCULATE ( DISTINCTCOUNT ( MasterProjListR02[Project Number] ), REMOVEFILTERS ( FYCalendar ), MasterProjListR02[Project Creation Date] >= LastWeekStart, MasterProjListR02[Project Creation Date] <= LastWeekEnd )and your measure for YTD will already work:
see attached file
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
arp2 - is that exactly how your data is?
Because 11/7/2018 is not a valid date, but 11/07/2018 is.
Go to Power Query and check if the Project Creation Date column is formatted as a date, if it's not, do so and I suspect you'll either fix this column, or you'll see a column full of errors (I have fixed the first 4 rows below):
Your DAX for the week calculation is missing the REMOVEFILTERS I suggested and your date parameters wont work, but the YTD one should work if we were using a date column.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
arp2 - Once you've fixed any date format issues present, this could work for your weekly calculation:
ProjectsLastWeek2506 =
VAR LastWeekStart =
CALCULATE(MAX( FYCalendar[WeekStart]) -7, REMOVEFILTERS( FYCalendar ))
VAR LastWeekEnd =
CALCULATE(MAX( FYCalendar[WeekEnding]) -7, REMOVEFILTERS( FYCalendar ))
RETURN
CALCULATE(
COUNTROWS(MasterProjListR02),
REMOVEFILTERS( FYCalendar ),
MasterProjListR02[Project Creation Date] >= LastWeekStart,
MasterProjListR02[Project Creation Date] <= LastWeekEnd
)
I say should, becuase I cant really test it, your project data is limited but goes back to 2016 and your date data only has very select dates for 2024 and 2025 - for example 27/07/2024 onwards are completely missing:
If this is the date data you are actually using, then it explains why your YTD calculation is not working as well - in order to use this function you need a table of continuous dates.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
arp2 - this will work for last week:
VAR LastWeekStart = SELECTEDVALUE ( FYCalendar[WeekStart] ) - 7 VAR LastWeekEnd = SELECTEDVALUE ( FYCalendar[WeekEnding] ) - 7 RETURN CALCULATE ( DISTINCTCOUNT ( MasterProjListR02[Project Number] ), REMOVEFILTERS ( FYCalendar ), MasterProjListR02[Project Creation Date] >= LastWeekStart, MasterProjListR02[Project Creation Date] <= LastWeekEnd )and your measure for YTD will already work:
see attached file
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
mark_endicott This is extremely helpful. Thank you for your support. Good Day!
