Forum Discussion

Power_Guy's avatar
Power_Guy
Frequent Visitor
1 year ago
Solved

Dynamic Dax for Previous

Hi
I am trying to return previous year quarter value  however only for one area dax is showing 0 

I wanted to modify my dax for Promote Previous measure Promote current is the first measure 
Promote Current is finding the maximum value across the quarter
For Instance : For 2028/q1 promote previous value should show 16538747.92 not 0 

I am attaching expected out put 



This is the dax which i used to create previous value

VAR PrevValue =
    CALCULATE(
        CALCULATE(MAX('Promote'[Promote (Advances)])),
        FILTER(
            ALL('Date'),
            'Date'[Period] < MAX('Date'[Period])
        )
    )
RETURN
    IF(
        ISBLANK(PrevValue),
        0,
        PrevValue
    )


Any help will be much appreciated.



 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    1 year ago

    Hi Power_Guy 

    If "Tax Distribution/Promote" is a column then you need to remove the filter from this column by wrapping it with ALL and include it as one of the CALCULATE arguments. 

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    1 year ago

    Hi Power_Guy 

    If "Tax Distribution/Promote" is a column then you need to remove the filter from this column by wrapping it with ALL and include it as one of the CALCULATE arguments. 

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Power_Guy  - Your current DAX formula does not explicitly filter for the previous quarter within the same category. Instead, it looks at the entire date table and finds the max value before the current period

     

    please find the revised measure as below:

    Promote Previous =
    VAR CurrentQuarter = MAX('Date'[Period])
    VAR PreviousQuarter =
    CALCULATE(
    MAX('Date'[Period]),
    FILTER(
    ALL('Date'),
    'Date'[Period] < CurrentQuarter
    )
    )
    RETURN
    CALCULATE(
    MAX('Promote'[Promote (Advances)]),
    FILTER(
    ALL('Date'),
    'Date'[Period] = PreviousQuarter
    )
    )

     

    check it , hope it works.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This pattern should work

    Measure = calculate([promote current],previousquarter(calendar[date]))

    Ensure that you drag quarter to the table visual from the Calendar table.  If this does not help, then share the download link of the PBi file and the problem there very clearly.