Running total by fiscal year

[christinek]

Hi all.

I am trying to get my running total called "YTD Jobs In" correct, but as a new Power BI user, after trying to research what is needed, it still doesn't work. This is what I have ended up with:

with measure on "YTD Jobs In" as follows:

YTD Jobs In =

CALCULATE (

COUNT(Workflow[Std Matter]),

filter(all(Workflow), Workflow[Fiscal Month]<= MAX(Workflow[Fiscal Month])))

Just in case it makes a difference, I have following for "Fiscal Month" measures:

Fiscal Month = format(Workflow[Mtr Act Job In Date].[Month], "Mmm")

Fiscal Month Sort =

var _month = month(Workflow[Mtr Act Job In Date])

var _result = IF(_month >6, _month-6, _month+6)

return

_result

Any assistance greatly appreciated.

[amitchandak · Accepted solution]

@christinek , One way it use datesytd

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"06/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"06/30"))

or

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = if(month(_max) <7, Date(year(_max)-1,7,1) ,Date(year(_max),7,1) )
return
CALCULATE(calculate([net],DATESBETWEEN('Date'[Date],_min,_max))

You can create FY in you calendar/date table and can use window with FY partition

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[christinek]

Thanks you so much. This guided me in setting up my dates correctly and I now have my running total working correctly. Appreciate your help.