Compare Month to Month Data

[gmasta1129]

Hello, I have a table that contains the following columns:

1. Run Date

2. Facility Code

3. NAV Date

4. Total NAV Value

I would like to create a measure to show the difference in "Total NAV Value" by NAV Date (month end date to month end date) and Facility Code. 

For example, Facility Code "11111", the difference between the "Total NAV Value" from "NAV Date" 5/30/2025 to 4/30/2025 is $5,000,000 (1,000,000 - 6,000,000) and the difference between "NAV Date" 4/30/2025 to 3/31/2025 is $2,000,000 (6,000,000-4,000,000).  

Run Date	Facility Code	NAV Date	Total NAV Value
6/1/2025	11111	5/31/2025	1,000,000.00
6/1/2025	22222	5/31/2025	5,000,000.00
5/25/2025	11111	4/30/2025	6,000,000.00
5/25/2025	22222	4/30/2025	20,000,000.00
4/10/2025	11111	3/31/2025	4,000,000.00
4/10/2025	22222	3/31/2025	15,000,000.00

[v-hjannapu · Accepted solution]

Hi @gmasta1129  ,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @vicky_ , for your response regarding the issue.

You can create the following DAX measure to calculate the NAV difference from the previous month for each Facility Code:

NAV Difference = 
VAR CurrentNAVDate = MAX('NAV_Data'[NAV Date])
VAR CurrentFacility = MAX('NAV_Data'[Facility Code])
VAR CurrentNAV =
    CALCULATE(
        SUM('NAV_Data'[Total NAV Value]),
        'NAV_Data'[Facility Code] = CurrentFacility,
        'NAV_Data'[NAV Date] = CurrentNAVDate
    )
VAR PrevNAVDate =
    CALCULATE(
        MAX('NAV_Data'[NAV Date]),
        FILTER(
            ALL('NAV_Data'),
            'NAV_Data'[Facility Code] = CurrentFacility &&
            'NAV_Data'[NAV Date] < CurrentNAVDate
        )
    )
VAR PrevNAV =
    CALCULATE(
        SUM('NAV_Data'[Total NAV Value]),
        'NAV_Data'[Facility Code] = CurrentFacility,
        'NAV_Data'[NAV Date] = PrevNAVDate
    )
RETURN
    IF(
        ISBLANK(PrevNAV),
        BLANK(),
        CurrentNAV - PrevNAV
    )

I tested it with my sample data, and it worked fine. Please find the attached screenshot and pbix for your reference.

If this helps solve your issue, kindly consider Accept it as a solution so other community members can find it useful too.
Reagrds,
Harshitha.

[gmasta1129]

Hello @v-hjannapu ,

The formula worked! Thank you so much for your help! 😀😀

[vicky_]

Try the below:

Difference = SUM('Table'[Total NAV Value]) - CALCULATE(SUM('Table'[Total NAV Value]), OFFSET(-1, DISTINCT(ALLSELECTED('Table')), ORDERBY('Table'[NAV Date], DESC), PARTITIONBY('Table'[Facility Code])))