Computing MTD, QTD, YTD in Power BI for the current period

[@elrevesyelderecho]

0:00 - 9:22 Intro 9:23 - 22:27 First Technique 22:28 - 35:50 Second Technique ( Calculation Group )

[@Lixia123]

This is exactly what I am looking for ! Thanks

[@mathew9665]

Nicely done - did I see a show dates with sales measure?!?

[@mtavassoti]

I don't think there are many medium to large size companies that care about calendar month or year. A lot of companies have structured financial calendar that's more every spread and logical. Therefore, I'd like to kindly ask for articles about how to deal with custom/financial calendars rather than built-in calendar calls, please. Thank you so much.

[@SQLBI]

We have dedicated patterns for that: www.daxpatterns.com/week-related-calculations/ www.daxpatterns.com/custom-time-related-calculations/

[@karbonita6464]

Hi Marco, I want to show in a table the last 13 months of percentage growth in horizontal table. Could MTD option can help me on this or is there another solution?

[@SQLBI]

Look at DATESINPERIOD ('Date'[Date], -13, MONTH)

[@houstonvanhoy7767]

2:38 Sales Amount should be outlined in red, not Delivered Amount.

[@josol45]

Excellent, subscribed

[@rashmishenai1495]

How would be performance if same calculation is done at database level and just picked in Power BI. Will calculation would be better ?

[@SQLBI]

The precalculated values would not by dynamic and based on the filters applied to the report...

[@carltonseymour869]

I liked the first method.

[@mogarrett3045]

excellent thank you for sharing

[@joelluis4938]

Hi! I've been always thinking about this possibility. I really enjoyed the concept of YoYTD and QoQTD. So this also means that I should only use MoM , QoQ and YoY with periods completed ? For example Oct Vs Sept . But Never Nov as we are in 7 Nov today and Nov is still in progress...

[@SQLBI]

Look at the measures in DAX Patterns, they restrict the comparison to the number of days for which you have data: www.daxpatterns.com/time-patterns/

[@shahzadkheros]

Amazing

[@Milhouse77BS]

Yay!

[@alvarovv9216]

Marco Russo > chat gpt 4.0

[@hichamezznati2646]

thank you so much for this amazing video . i would like to share 02 ways i found to solve the pb : with and without times intelligence functions. 1) first way : using times intelligence functions for X ∈ {MTD,QTD,YTD} : X Sales Amount= VAR DatesBis= CALENDAR( Date(year(LastTransactionDate),1,1), LastDatewithsales ) return CALCULATE( [Sales Amount], DATESX(TREATAS(DatesBis,'Date'[Date])) ) 2) 2nd way : withoud times intelligence functions + MTD Sales Amount = VAR firstOfCurrentMonth=EOMONTH(LastTransactionDate,-1) + 1 VAR _DatesMTD=CALENDAR(firstOfCurrentMonth,LastTransactionDate) return CALCULATE( [Sales Amount], TREATAS(_DatesMTD,'Date'[Date]) ) + QTD Sales Amount = VAR MonthMod3=MOD(MONTH(LastTransactionDate),3) VAR PathToFirstOfCurrentQuarter=if(MonthMod3=0,-3,-MonthMod3) VAR FirstOfCurrentQuarter=EOMONTH(LastTransactionDate,PathToFirstOfCurrentQuarter) + 1 VAR _DatesQTD=CALENDAR(FirstOfCurrentQuarter,LastTransactionDate) return CALCULATE( [Sales Amount], TREATAS(_DatesQTD,'Date'[Date]) ) + YTD sales Amount= VAR _DatesYTD= CALENDAR( Date(year(LastTransactionDate),1,1), LastTransactionDate ) return calculate( [Sales Amount], TREATAS(_DatesYTD,'Date'[Date]) )