Calculating Sales Financial Year To Date In Power BI With DAX [2022 Update] 

Hello Rick, In that case, you'll be required to create a date table first in accordance with the scenario that you've mentioned where FY starts on 1st March. Below is the DAX based date table code provided: Date = VAR FirstFiscalMonth = 3 -- First month of the fiscal year VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ... VAR FirstSalesDate = MIN ( Sales[Order Date] ) VAR LastSalesDate = MAX ( Sales[Order Date] ) VAR FirstFiscalYear = -- Customizes the first fiscal year to use YEAR ( FirstSalesDate ) + 1 * ( MONTH ( FirstSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1) VAR LastFiscalYear = -- Customizes the last fiscal year to use YEAR ( LastSalesDate ) + 1 * ( MONTH ( LastSalesDate ) >= FirstFiscalMonth && FirstFiscalMonth > 1) RETURN GENERATE ( VAR FirstDay = DATE ( FirstFiscalYear - 1 * (FirstFiscalMonth > 1), FirstFiscalMonth, 1 ) VAR LastDay = DATE ( LastFiscalYear + 1 * (FirstFiscalMonth = 1), FirstFiscalMonth, 1 ) - 1 RETURN CALENDAR ( FirstDay, LastDay ), VAR CurrentDate = [Date] VAR Yr = YEAR ( CurrentDate ) -- Year Number VAR Mn = MONTH ( CurrentDate ) -- Month Number (1-12) VAR Mdn = DAY ( CurrentDate ) -- Day of Month VAR DateKey = Yr*10000+Mn*100+Mdn VAR Wd = -- Weekday Number (0 = Sunday, 1 = Monday, ...) WEEKDAY ( CurrentDate + 7 - FirstDayOfWeek, 1 ) VAR WorkingDay = -- Working Day (1 = working, 0 = non working) ( WEEKDAY ( CurrentDate, 1 ) IN { 2, 3, 4, 5, 6 } ) VAR Fyr = -- Fiscal Year Number Yr + 1 * ( FirstFiscalMonth > 1 && Mn >= FirstFiscalMonth ) VAR Fmn = -- Fiscal Month Number (1-12) Mn - FirstFiscalMonth + 1 + 12 * (Mn < FirstFiscalMonth) VAR Fqrn = -- Fiscal Quarter (string) ROUNDUP ( Fmn / 3, 0 ) VAR Fmqn = MOD ( FMn - 1, 3 ) + 1 VAR Fqr = -- Fiscal Quarter (string) FORMAT ( Fqrn, "\Q0" ) VAR FirstDayOfYear = DATE ( Fyr - 1 * (FirstFiscalMonth > 1), FirstFiscalMonth, 1 ) VAR Fydn = SUMX ( CALENDAR ( FirstDayOfYear, CurrentDate ), 1 * ( MONTH ( [Date] ) 2 || DAY ( [Date] ) 29 ) ) RETURN ROW ( "DateKey", INT ( DateKey ), "Sequential Day Number", INT ( [Date] ), "Year Month", FORMAT ( CurrentDate, "mmm yyyy" ), "Year Month Number", Yr * 12 + Mn - 1, "Fiscal Year", "FY " & Fyr, "Fiscal Year Number", Fyr, "Fiscal Year Quarter", "F" & Fqr & "-" & Fyr, "Fiscal Year Quarter Number", CONVERT ( Fyr * 4 + FQrn - 1, INTEGER ), "Fiscal Quarter", "F" & Fqr, "Month", FORMAT ( CurrentDate, "mmm" ), "Fiscal Month Number", Fmn, "Fiscal Month in Quarter Number", Fmqn, "Day of Week", FORMAT ( CurrentDate, "ddd" ), "Day of Week Number", Wd, "Day of Month Number", Mdn, "Day of Fiscal Year Number", Fydn, "Working Day", IF ( WorkingDay, "Working Day", "Non-Working Day" ) ) ) Now, write the two measures provided as follows to achieve the results - ShowValueForDates = VAR LastDateWithData = CALCULATE ( MAX ( 'Sales'[Order Date] ), REMOVEFILTERS () ) VAR FirstDateVisible = MIN ( 'Date'[Date] ) VAR Result = FirstDateVisible

Hi Pratapchit, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

Hi Ankita, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

Hi Wadood Abdul, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

Hi Gopy J S, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

Hi Mathy, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!