Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Previous Year/quarter sales

Hi everyone,

I'm working on a Power BI report and need some help with calculating sales for the previous year/quarter based on a selected period. My Year column is in the format YYYY/Qn (e.g., 2024/Q4), and I'm facing a challenge because this column is a text data type.

Scenario: If I select 2024/Q4, I want to see the sales for 2024/Q3. However, due to the text data type of the Year column, I'm encountering difficulties in achieving this.


Current DAX Formula:

Testing =
VAR _SelectedYear = LEFT(SELECTEDVALUE('Date'[Year]), 4) - 1
VAR _SelectedQuarter = RIGHT(SELECTEDVALUE('Date'[Year]), 2)
RETURN
CALCULATE(
    [#1 GAV(USD)],
    'Date'[Year] = _SelectedYear & "/Q" & _SelectedQuarter
)

Issue: The formula above results in a syntax error. I'm looking for ideas and support to correct this and achieve the desired output.

Expected Output: When selecting 2024/Q4, the report should display sales for 2024/Q3.

Any suggestions or guidance would be greatly appreciated!

 




  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous , Try using

    DAX
    PreviousQuarterSales =
    VAR _SelectedYearQuarter = SELECTEDVALUE('Date'[Year])
    VAR _SelectedYear = LEFT(_SelectedYearQuarter, 4)
    VAR _SelectedQuarter = RIGHT(_SelectedYearQuarter, 2)
    VAR _PreviousYearQuarter =
    SWITCH(
    _SelectedQuarter,
    "Q1", (VALUE(_SelectedYear) - 1) & "/Q4",
    "Q2", _SelectedYear & "/Q1",
    "Q3", _SelectedYear & "/Q2",
    "Q4", _SelectedYear & "/Q3"
    )
    RETURN
    CALCULATE(
    [#1 GAV(USD)],
    'Date'[Year] = _PreviousYearQuarter
    )

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Anonymous , Try using

    DAX
    PreviousQuarterSales =
    VAR _SelectedYearQuarter = SELECTEDVALUE('Date'[Year])
    VAR _SelectedYear = LEFT(_SelectedYearQuarter, 4)
    VAR _SelectedQuarter = RIGHT(_SelectedYearQuarter, 2)
    VAR _PreviousYearQuarter =
    SWITCH(
    _SelectedQuarter,
    "Q1", (VALUE(_SelectedYear) - 1) & "/Q4",
    "Q2", _SelectedYear & "/Q1",
    "Q3", _SelectedYear & "/Q2",
    "Q4", _SelectedYear & "/Q3"
    )
    RETURN
    CALCULATE(
    [#1 GAV(USD)],
    'Date'[Year] = _PreviousYearQuarter
    )