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 )

[@mathew9665]

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

[@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)

[@Lixia123]

This is exactly what I am looking for ! Thanks

[@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/

[@houstonvanhoy7767]

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

[@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...

[@alvarovv9216]

Marco Russo > chat gpt 4.0

[@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/

[@josol45]

Excellent, subscribed

[@carltonseymour869]

I liked the first method.

[@mogarrett3045]

excellent thank you for sharing

[@shahzadkheros]

Amazing

[@Milhouse77BS]

Yay!

[@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])
)