Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I created a periods table that is based on my date table that is the Min(Prod Date) up to the Max(Prod Date) which is 6/30/2023. So I have dates from the beginnging of 2018 till 6/30/2023 currently. When I use the PreviousQuarter function it is returning the dates from 10/31/2022 - 12/31/2022. But from my understanding with the data I have the dates should be 1/31/2023, 2/28/2023, and 3/31/2023 for Q1. But instead it is returning Q4 of 2022. I have attached snip its along with my question and the dax behind my Periods table.
Solved! Go to Solution.
Hi @Power5 ,
You can create a calculated column as below to get it, please find the details in the attachment.
Type =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _year =
YEAR ( _maxdate )
VAR _qtr =
QUARTER ( _maxdate )
RETURN
SWITCH (
TRUE (),
'Date Table'[Year] = _year
&& 'Date Table'[Month Number] = MONTH ( _maxdate ), "MTD",
'Date Table'[Year]
= IF ( _qtr = 1, _year - 1, _year )
&& 'Date Table'[Quarter]
= IF ( _qtr = 1, 4, _qtr - 1 ), "Last Quarter",
'Date Table'[Year] = _year
&& 'Date Table'[Quarter] = _qtr, "QTD",
'Date Table'[Year] = _year - 1, "Last Year",
'Date Table'[Year] = _year, "YTD"
)
If you still want to get a calculated table, you can update the formula of the calculated table [Dates Periods] as below:
Date Periods =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _year =
YEAR ( _maxdate )
VAR _qtr =
QUARTER ( _maxdate )
RETURN
ADDCOLUMNS (
'Date Table',
"Type",
SWITCH (
TRUE (),
'Date Table'[Year] = _year
&& 'Date Table'[Month Number] = MONTH ( _maxdate ), "MTD",
'Date Table'[Year]
= IF ( _qtr = 1, _year - 1, _year )
&& 'Date Table'[Quarter]
= IF ( _qtr = 1, 4, _qtr - 1 ), "Last Quarter",
'Date Table'[Year] = _year
&& 'Date Table'[Quarter] = _qtr, "QTD",
'Date Table'[Year] = _year - 1, "Last Year",
'Date Table'[Year] = _year, "YTD"
)
)
Best Regards
Hi @Power5 ,
You can create a calculated column as below to get it, please find the details in the attachment.
Type =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _year =
YEAR ( _maxdate )
VAR _qtr =
QUARTER ( _maxdate )
RETURN
SWITCH (
TRUE (),
'Date Table'[Year] = _year
&& 'Date Table'[Month Number] = MONTH ( _maxdate ), "MTD",
'Date Table'[Year]
= IF ( _qtr = 1, _year - 1, _year )
&& 'Date Table'[Quarter]
= IF ( _qtr = 1, 4, _qtr - 1 ), "Last Quarter",
'Date Table'[Year] = _year
&& 'Date Table'[Quarter] = _qtr, "QTD",
'Date Table'[Year] = _year - 1, "Last Year",
'Date Table'[Year] = _year, "YTD"
)
If you still want to get a calculated table, you can update the formula of the calculated table [Dates Periods] as below:
Date Periods =
VAR _maxdate =
MAX ( 'Date Table'[Date] )
VAR _year =
YEAR ( _maxdate )
VAR _qtr =
QUARTER ( _maxdate )
RETURN
ADDCOLUMNS (
'Date Table',
"Type",
SWITCH (
TRUE (),
'Date Table'[Year] = _year
&& 'Date Table'[Month Number] = MONTH ( _maxdate ), "MTD",
'Date Table'[Year]
= IF ( _qtr = 1, _year - 1, _year )
&& 'Date Table'[Quarter]
= IF ( _qtr = 1, 4, _qtr - 1 ), "Last Quarter",
'Date Table'[Year] = _year
&& 'Date Table'[Quarter] = _qtr, "QTD",
'Date Table'[Year] = _year - 1, "Last Year",
'Date Table'[Year] = _year, "YTD"
)
)
Best Regards
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |