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 - At a guess, I would say you're not removing the filter context from your Fiscal Year Calendar and then re-adding the new context you wish to calculate. In order to do this you can use REMOVEFILTERS( Fiscal Year Calendar )
Here's an example:
VAR prev_week_start = SELECTEDVALUE( DateTable[Previous Week Start] )
VAR prev_week_end = SELECTEDVALUE( DateTable[Previous Week End] )
RETURN
CALCULATE( [Measure], REMOVEFILTERS( DateTable ), Table[Date] >= prev_week_start && Table[Date] <= prev_week_end )
However this is just a guess, because you've not provided your DAX that's returning 0 so.......
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Thnx mark_endicott I have pasted sample tables here above and the measures I have created (but not working).
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!