cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate and show YTD values month by month with fiscal year unequal calendar year

Dear everybody,

our business year starts in April.

I am trying to show ytd revenues every month sorted by product and source with this formula

QTY YTD = CALCULATE ((TOTALYTD(SUM('Sales'[ QTY]), 'Sales'[Date].[Date], ALLEXCEPT ( 'Sales', 'Sales'[Source], 'Sales'[Product]))))

Where do i insert the stop date of 31.03 to prevent the formula from counting after the 31st of March?

Please see my project file with sample data under the following link

1 ACCEPTED SOLUTION
Super User

@cerebro , Use a date table joined with date of you table and create measure like example

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

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

You can also consider the window function

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

2 REPLIES 2
Super User

@cerebro , Use a date table joined with date of you table and create measure like example

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

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

You can also consider the window function

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

Frequent Visitor

thanks a lot. i implemented your solution and it worked.

will power bi always need this special "date table" to work with measures that require date intervalls?