cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure Difference Between Cumulative Measure

I have created the following code

``````FTE_Prev_Mnth =
CALCULATE(
[FTE_Demand],
PREVIOUSMONTH(Date_Dim[Date]) , Date_Dim[Date] <= MAX(Date_Dim[Date]) )``````

Which then goes into

``FTE_Mnth_Var = CALCULATE([FTE_Demand] - [FTE_Prev_Mnth] )``

The output is:

 Year Month FTE_Demand FTE_Mnth_Var 2024 May 626.5572 1.773035 2024 June 625.0259 -1.53131 2024 July 648.6083 23.58244 2024 August 655.1697 6.561367 2024 September 655.0414 -0.12826 2024 October 676.5294 21.48798 2024 November 684.7248 8.195445 2024 December 689.8698 5.145005 2025 January 668.7971 -21.0728 2025 February 675.9296 7.132578 2025 March 685.1627 9.233071 2025 April 691.6453 6.482635 2025 May 629.0432 -62.6022 2025 June 631.6405 2.597319 2025 July 613.5211 -18.1194 2025 August 616.9264 3.405306

How can i add the different months of FTE_Mnth_Var together bearing in mind they are a measure and not calculated column - i want to see the difference between for example june 2024 &  March 2025 using my Date_Dim Table. Also the 1.77 showing in May 2024 shouldnt be showing  amonth variance if that month isnt visible?

Thank you

Jack

1 ACCEPTED SOLUTION
Community Support

Hi,@pbijack93

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. First of all, about your line value does not exist will not be counted, I recommend that you use the ISBLANK () function, his role is to first determine whether the value is empty, it is recommended that you can modify your measure to the following code:

``````TE_Mnth_Var=
IF(
ISBLANK([FTE_Prev_Mnth]),
BLANK(),
[FTE_Demand] - [FTE_Prev_Mnth]
)
``````

Here is the relevant documentation:
ISBLANK function (DAX) - DAX | Microsoft Learn

3.Secondly, if you want to count the values in the date interval you set (the values come from a measure), you can also use the Sumx() function, here is the measure I created to meet your needs:

``````Measure = SUMX('Date_Dim',CALCULATE([FTE_Mnth_Var],FILTER('Date_Dim','Date_Dim'[Date]>DATE(2024,6,1)&&'Date_Dim'[Date]<DATE(2025,3,1))))

``````

Here is the documentation for it：

SUMX function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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

Community Support

Hi,@pbijack93

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. First of all, about your line value does not exist will not be counted, I recommend that you use the ISBLANK () function, his role is to first determine whether the value is empty, it is recommended that you can modify your measure to the following code:

``````TE_Mnth_Var=
IF(
ISBLANK([FTE_Prev_Mnth]),
BLANK(),
[FTE_Demand] - [FTE_Prev_Mnth]
)
``````

Here is the relevant documentation:
ISBLANK function (DAX) - DAX | Microsoft Learn

3.Secondly, if you want to count the values in the date interval you set (the values come from a measure), you can also use the Sumx() function, here is the measure I created to meet your needs:

``````Measure = SUMX('Date_Dim',CALCULATE([FTE_Mnth_Var],FILTER('Date_Dim','Date_Dim'[Date]>DATE(2024,6,1)&&'Date_Dim'[Date]<DATE(2025,3,1))))

``````

Here is the documentation for it：

SUMX function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors