Skip to main content
cancel
Showing results for 
Search instead 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

Reply
pbijack93
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:

 

YearMonthFTE_DemandFTE_Mnth_Var
2024May626.55721.773035
2024June625.0259-1.53131
2024July648.608323.58244
2024August655.16976.561367
2024September655.0414-0.12826
2024October676.529421.48798
2024November684.72488.195445
2024December689.86985.145005
2025January668.7971-21.0728
2025February675.92967.132578
2025March685.16279.233071
2025April691.64536.482635
2025May629.0432-62.6022
2025June631.64052.597319
2025July613.5211-18.1194
2025August616.92643.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
v-linyulu-msft
Community Support
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:

vlinyulumsft_0-1716440355302.png

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.

View solution in original post

1 REPLY 1
v-linyulu-msft
Community Support
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:

vlinyulumsft_0-1716440355302.png

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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