DAX Formula - 1 month from date, 3 months from date and so on

[gmasta1129]

I have one table which contains the following columns

Column 1 -  DATETIME

Column 2 - FACILITY CODE

Column 3 -  NAV

Column 4 -  NAV Period END DATE

In this report, i will have a dropdown slicer using column 1) DATETIME data

Please note, this report runs on a daily basis and therefore the last NAV will need to pull in.  For example, if the reports runs each day for the entire month, column 4 NAV Period End Date can be 11/30/2021 for each day of the entire month.  Therefore the last/latest date NAV value (column 3) will need to pull in for 11/30/2021.  See file below for more details  

I would like to create additional calculated columns.  As of the selected dropdown date (ex: 1/22/2021), i would like to take the NAV value (column 3)  1 month from the NAV Period End Date (column 4). Then in another column, pull the NAV Period END DATE 1 month from the NAV Period END Date (column 4). Both columns need to be done by Facility Code.  ex: Facility code = 1 pulls only Facility code = 1 NAV Value Then i would need this for 3 months, 6 months an 12 months.    

[gmasta1129]

Hello,

@v-zhangti Thanks for helping with the formula. I changed the formula slightly where  

  = EARLIER ( 'Table'[DATETIME] ) - 1

To =EARLIER ( 'Table'[NAV PERIOD END DATE] ) -1

For the example above, it looks like the 3 month formula is pulling in 11/30/2021 instead of 9/30/2021. The facility code i picked for the example does not have 9/30/2021 nav date/nav data and therefore it should pull in zero.  Not sure why it is pulling in 11/30/2021.  

[v-zhangti]

Hi, @gmasta1129 

The column you expect to calculate can be obtained from the following formula.

Column:

1MONTH NAV FROM LATEST NAV PERIOD = 
IF (
    [DATETIME] = MAX ( 'Table'[DATETIME] ),
    CALCULATE (
        MAX ( 'Table'[NAV] ),
        FILTER (
            'Table',
            [DATETIME]
                = EARLIER ( 'Table'[DATETIME] ) - 1
                && [FACILITY CODE] = EARLIER ( 'Table'[FACILITY CODE] )
        )
    ),
    BLANK ()
)

1M FROM LATEST NAV PERIOD = 
IF (
    [DATETIME] = MAX ( 'Table'[DATETIME] ),
    CALCULATE (
        MAX ( 'Table'[NAV PERIOD END DATE] ),
        FILTER (
            'Table',
            [DATETIME]
                = EARLIER ( 'Table'[DATETIME] ) - 1
                && [FACILITY CODE] = EARLIER ( 'Table'[FACILITY CODE] )
        )
    ),
    BLANK ()
)

3MONTH NAV FROM LATEST NAV PERIOD = 
IF (
    [DATETIME] = MAX ( 'Table'[DATETIME] ),
    CALCULATE (
        MAX ( 'Table'[NAV] ),
        FILTER (
            'Table',
            [DATETIME]
                = EARLIER ( 'Table'[DATETIME] ) - 3
                && [FACILITY CODE] = EARLIER ( 'Table'[FACILITY CODE] )
        )
    ),
    BLANK ()
)

3M FROM LATEST NAV PERIOD = 
IF (
    [DATETIME] = MAX ( 'Table'[DATETIME] ),
    CALCULATE (
        MAX ( 'Table'[NAV PERIOD END DATE] ),
        FILTER (
            'Table',
            [DATETIME]
                = EARLIER ( 'Table'[DATETIME] ) - 3
                && [FACILITY CODE] = EARLIER ( 'Table'[FACILITY CODE] )
        )
    ),
    BLANK ()
)

Is this the result you expected? What other calculations would you like to do next? If you need our help, please reply with more details.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Why does it need to be a calculated column? What are you doing with the results?

[gmasta1129]

The results will be multiplied by a specific column (percentage %) in the table.