Forum Discussion
Compare Month to Month Data
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 |
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.
3 Replies
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.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])))
