Forum Discussion

jpbi23's avatar
jpbi23
Icon for Helper I rankHelper I
1 year ago

using future quarter values in a measure calculation

I have a table called MasterAll where I have built these 3 measures, 

 

 

I have a matrix visual that shows these 3 measures by fiscal quarters. I want to write another measure (FinalTotal) that would take, 

[Booked / Gross Retained $] - [Out-Qtr Late Booked $] + [Out-Qtr LateBooked $]--> value from the next quarter

For example, in 2023-Q1, it would be something like this, 

 

[60,347,535] - [3,209,341] + [1,026,289] --> grabbing 2023-Q2 value. Always should get this value from the next quarter

For another example, in 2023-Q2, it would be something like this, 

 

[72,782,964] - [1,206,289] + [2,620,736] -- grabbing 2023-Q3 value. 

 

is there a way to write this?

 

Thanks,

Jay

Need Help
Show and Tell
Tips and Tricks

2 Replies

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

    jpbi23 You will need to use the CALCULATE function along with the NEXTQUARTER function to get the value from the next quarter

     

    DAX
    FinalTotal =
    VAR CurrentQuarter = SELECTEDVALUE('MasterAll'[Fiscal Quarter])
    VAR NextQuarter = CALCULATE(
    SUM('MasterAll'[Out-Qtr LateBooked $]),
    NEXTQUARTER('MasterAll'[Fiscal Quarter])
    )
    RETURN
    SUM('MasterAll'[Booked / Gross Retained $]) -
    SUM('MasterAll'[Out-Qtr Late Booked $]) +
    NextQuarter

    • jpbi23's avatar
      jpbi23
      Icon for Helper I rankHelper I
      1 year ago

      Hey thanks for the response bhanu_gautam 

      The fiscal quarter column is not a datetype column. It's in this format "2023-Q1", "2023-Q2". NextQuarter function is not working due to this. Is there another workaround?

       

      Jay