Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Modify DAX Code to get Year-To-Date total

Hi, the code below is to show the Total Actuals (on a PBI chart) for a project through the most recent month ended (e.g. June 2022). 

Total Amount Actuals =
IF([Max Date] >= [Current Month Offset],
BLANK(),
CALCULATE([CapEx Actuals],
    FILTER(ALLSELECTED('Dim Date'[End of Month]),
        'Dim Date'[End of Month] <= MAX('Dim Date'[End of Month]))))
 
How can I modify this code so that it shows Total Actuals through today's date? 
So, for example,  instead of showing Jan-Jun 2022 Total Actuals, the DAX code would give the Jan-Jul 18 Total Actuals (July 18 being today's date)?

Thank you!
Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    In your calendar table create a calculated column that computes if a date in a prior period (month, quarter, year) should be considered . Have the table in import mode, refreshed daily.

     

    Then you can include that flag in your filters like so:

     

    PY = 
CALCULATE (
    [Sales],
    SAMEPERIODLASTYEAR ( Dates[date] ),
    Dates[IsPastPY] = TRUE
)

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    How do you calculate [Max Date] and [Current Month Offset]? Maybe you can try the measure.

     

    Total Amount Actuals =
IF (
    [Max Date] > [Current Month Offset],
    BLANK (),
    CALCULATE (
        [CapEx Actuals],
        FILTER ( ALLSELECTED ( 'Dim Date' ), 'Dim Date'[Date] <= TODAY () )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.