Forum Discussion

arp2's avatar
arp2
Helper II
1 year ago
Solved

Custom Fiscal Year error

I have a simple model in Power BI with two tables. I have a custom Fiscal Year Calendar Table which is Date Table. The FY starts in July and ends in June. The week starts on Saturday and ends on Frid...
Need Help
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    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's avatar
mark_endicott
Super User
1 year ago

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!

mark_endicott's avatar
mark_endicott
Super User
1 year ago

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's avatar
    arp2
    Helper II
    1 year ago

    mark_endicott This is extremely helpful. Thank you for your support. Good Day!