Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX function

Hey all, Im trying to create a Running Total (RT) function for Actuals, Budget and Forecast data. I have included the following function: 

Actual RT = calculate(sum(Actuals[Actual]), Filter(ALLSELECTED('calendar'[Date]), ISONORAFTER('calendar'[Date], MAX(Actuals[Date], desc))))   ---) I received the following error as a result

Any suggestions to why this maybe the case? TIA!

amitchandak MFelix Mikelytics Greg_Deckler JihwanKim 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    MFelix this worked for me:

     

    Actual RT = Calculate(SUM(Actuals[Actual]),FILTER(ALLSELECTED('Calendar'[Date]), ISONORAFTER('Calendar'[Date],MAX(Actuals[Date]), DESC)))

6 Replies

  • JW_van_Holst's avatar
    JW_van_Holst
    Resolver IV
    3 years ago

    ISONORAFTER is a utility function. Do not use it in measures. 
    Try:

    RT =
VAR LastVisibleDate =
    MAX ( 'calendar'[Date] )
VAR PeriodToUse =
    FILTER ( ALL ( 'calendar'[Date] ), 'calendar'[Date] <= LastVisibleDate )
RETURN
    CALCULATE ( SUM ( Actuals[Actual] ), PeriodToUse )

     

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi Anonymous ,

     

    Believe you have the syntax of the ISONORAFTER incorrect try the folllwing:

     

    Actual RT =
CALCULATE (
    SUM ( Actuals[Actual] ),
    FILTER (
        ALLSELECTED ( 'calendar'[Date] ),
        ISONORAFTER ( MAX ( 'calendar'[Date] ), desc )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      MFelix this worked for me:

       

      Actual RT = Calculate(SUM(Actuals[Actual]),FILTER(ALLSELECTED('Calendar'[Date]), ISONORAFTER('Calendar'[Date],MAX(Actuals[Date]), DESC)))
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks MFelix  JW_van_Holst for your responses. Ive had a slightly different issue. My pc required a restart to install updates and I didnt save the file. To recover the file, I reopened the file from Autorecovery in PowerBI and when I did Im stuck with this page. Any idea how this can be recovered to its previous state?

     

     

     

    • MFelix's avatar
      MFelix
      Super User
      3 years ago

      Hi Anonymous ,

       

      If the recovery file is also "broken" you don't have any way to get the recovered file. Try tomcheck the folder where the file is saved.

       

      The folder is on the options of the PBI. but be aware that this are temp files and since you open it it may be already deleted.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    MFelix thats correct I couldnt recover the file since it it was broken.