Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

The PreviousQuarter function is not pulling the correct dates back in my table?

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. 

 

 

 

 

General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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