Forum Discussion
YTD/QTD/MTD table
Hi,
I have created a table that calculate dates for YTD, QTD and MTD, but there seems to be something wrong, because only the YTD gives me the dates for the current year, the QTD and MTD give me much more dates than it should.
Can someone tell me what is wrong?
YTD/QTD/MTD =
var today=TODAY()
var start_of_year=CALCULATE(STARTOFYEAR('Dim Date'[Date]),YEAR('Dim Date'[Date])=YEAR(today))
var start_of_quarter=CALCULATE(STARTOFQUARTER('Dim Date'[Date]),QUARTER('Dim Date'[Date])=QUARTER(today))
var start_of_month=CALCULATE(STARTOFMONTH('Dim Date'[Date]),MONTH('Dim Date'[Date])=MONTH(today))
var finalresult=
UNION(ADDCOLUMNS(
CALENDAR(start_of_year,today),
"selection","YTD"
),
ADDCOLUMNS(
CALENDAR(start_of_quarter,today),
"selection","QTD"
),
ADDCOLUMNS(
CALENDAR(start_of_month,today),
"selection","MTD"
)
)return finalresult
3 Replies
- daxer-almighty
Solution Sage
Yeah... You're doing this:
CALCULATE(STARTOFQUARTER('Dim Date'[Date]),QUARTER('Dim Date'[Date])=QUARTER(today))
without paying attention to the fact that quarters exist in different years and have the same numbers. You're missing a filter on the year column.
- daxer-almighty