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) ...
Need Help
Show and Tell
Tips and Tricks
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