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

How to use the DAX time intelligence calculations applied to the latest period available in the data, also known as the “current” period.
Article and download: sql.bi/817168?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...

[@alvarovv9216]

Marco Russo > chat gpt 4.0

[@houstonvanhoy7767]

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

[@elrevesyelderecho]

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

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

[@Lixia123]

This is exactly what I am looking for ! Thanks

[@mogarrett3045]

excellent thank you for sharing

[@carltonseymour869]

I liked the first method.

[@shahzadkheros]

Amazing

[@mathew9665]

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

[@josol45]

Excellent, subscribed

[@Milhouse77BS]

Yay!

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

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

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