Try to to help me out exact dax query

To achieve the quarter average amount in DAX while handling the scenario of showing null when there is no previous quarter, you can follow these steps:

1. First, you need to create a calculated column or measure in your DAX model that calculates the average amount for each quarter based on the Year and Amount columns.

2. To calculate the quarter average amount, you can use the following DAX expression as a measure:


QuarterAverageAmount = 
CALCULATE(
    AVERAGE('YourTable'[Amount]),
    ALLEXCEPT('YourTable', 'YourTable'[Year], 'YourTable'[Quarter])
)

3. This DAX expression will calculate the average amount for each quarter by considering the Year and Quarter columns while ignoring any other filters that might be applied.

4. To handle the scenario where there is no previous quarter and show it as null, you can modify the expression to include a condition using the PREVIOUSQUARTER function. If the previous quarter does not exist, it will return a blank value.

5. Here is an example of how you can adjust the DAX expression to handle this scenario:


QuarterAverageAmount = 
IF(
    ISBLANK(PREVIOUSQUARTER('YourTable'[Date])),
    BLANK(),
    CALCULATE(
        AVERAGE('YourTable'[Amount]),
        ALLEXCEPT('YourTable', 'YourTable'[Year], 'YourTable'[Quarter])
    )
)

By incorporating the PREVIOUSQUARTER function and an IF statement, you can ensure that when there is no previous quarter, the QuarterAverageAmount will be displayed as null.

Feel free to adjust the DAX expression based on your specific table and column names. Remember to replace 'YourTable', 'Year', 'Quarter', and 'Amount' with your actual table and column names in your dataset.

I hope this guidance helps you to accomplish your goal in calculating the quarter average amount in DAX with the provision to show null when there is no previous quarter. If you have any further questions or need additional assistance, feel free to ask!

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sophia Carter1y ago
To achieve the quarter average amount in DAX while handling the scenario of showing null when there is no previous quarter, you can follow these steps:

1. First, you need to create a calculated column or measure in your DAX model that calculates the average amount for each quarter based on the Year and Amount columns.

2. To calculate the quarter average amount, you can use the following DAX expression as a measure:
```
QuarterAverageAmount = 
CALCULATE(
    AVERAGE('YourTable'[Amount]),
    ALLEXCEPT('YourTable', 'YourTable'[Year], 'YourTable'[Quarter])
)
```
3. This DAX expression will calculate the average amount for each quarter by considering the Year and Quarter columns while ignoring any other filters that might be applied.

4. To handle the scenario where there is no previous quarter and show it as null, you can modify the expression to include a condition using the PREVIOUSQUARTER function. If the previous quarter does not exist, it will return a blank value.

5. Here is an example of how you can adjust the DAX expression to handle this scenario:
```
QuarterAverageAmount = 
IF(
    ISBLANK(PREVIOUSQUARTER('YourTable'[Date])),
    BLANK(),
    CALCULATE(
        AVERAGE('YourTable'[Amount]),
        ALLEXCEPT('YourTable', 'YourTable'[Year], 'YourTable'[Quarter])
    )
)
```
By incorporating the PREVIOUSQUARTER function and an IF statement, you can ensure that when there is no previous quarter, the QuarterAverageAmount will be displayed as null.

Feel free to adjust the DAX expression based on your specific table and column names. Remember to replace 'YourTable', 'Year', 'Quarter', and 'Amount' with your actual table and column names in your dataset.

I hope this guidance helps you to accomplish your goal in calculating the quarter average amount in DAX with the provision to show null when there is no previous quarter. If you have any further questions or need additional assistance, feel free to ask!
0