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

Sam demonstrates how to get financial year to date calculations in Power BI with DAX. A simple formula that can produce great insight fast.
**** Video Details ****
00:46 Creating Total Sales Table
02:00 Calculating Sales Year To Date
03:20 Changing Calculation To Sales Financial Year To Date
04:36 Creating A Visualisation For The Existing Calculation
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - www.enterprised...
FREE - 60 Page DAX Reference Guide Download - www.enterprise...
Enterprise DNA Membership - www.enterprise...
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - www.enterprise...
#EnterpriseDNA #PowerBI #PowerBIDesktop #PowerBITutorial #DAX #DAXTutorial

[@Andrew-yy6jf]

So helpful, thank you!

[@pratapchitdumrongsakul2167]

Thank you.. :)

[@EnterpriseDNA]

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!

[@WadoodAbdul-im6gz]

Thank you so much Boss!!!!!

[@EnterpriseDNA]

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!

[@MrRickstar2010]

Thank you so much for the video!
How would I handle a FY that starts on the 1st March and be able to deal with leap years (different end dates for February)?

[@EnterpriseDNA]

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

[@somnathdey6475]

Thanks for your video....how can we do the same thing for quater, half yearly

[@welcometomathy]

Great!

[@EnterpriseDNA]

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!

[@gopyjs6216]

Great👌👌

[@EnterpriseDNA]

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!

[@ShahidKhan-up2ft]

i want to know dax for total sales of 2014 year only

[@ankitamalhotra5761]

👍👍

[@EnterpriseDNA]

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!