Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Stop Difference-calculation between periodes that are in the future

Hello,

 

How can i stop power bi in calculation values against eachother in future periods where in 2019 the value is blank?

In the picture below period 9 till 13 are calculated for 2018 against 2019, but there arent any values for 2019 yet.

The totals are influenced by these values at "Difference 2018 / 2019" and "%Difference 2018 / 2019".

 

How do i include a BLANK filter in de dax functions?

dax funtions are in the pichure below the values.

 

picture.PNG

9 REPLIES 9
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

I created a sample that you can have a try. 

 

Diff = 
var max_mon = CALCULATE(MAX('Table'[period]),FILTER('Table','Table'[Cost 2019] <>BLANK()))
return 
CALCULATE(SUM('Table'[Cost 2019]) - SUM('Table'[Cost 2018]),FILTER('Table','Table'[period] <= max_mon))

% Diff = DIVIDE([Diff],SUM('Table'[Cost 2018])) 

6.PNG

 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-xuding-msft 

Thank you for your reply. Is see that you are using columns. i forgot to mention that i am using measures.

All columns in my picure are measures exept for period.

 

Is there also a code that goes with measures?

With measures i dont have to store calculated data in my model.

Hi @Anonymous ,

The formulas are both measures. And I attached the file that you can check if it is what you want. 

 

Best Regards,

Xue Ding

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello @v-xuding-msft ,

 

I looked at the measures. but they have a source column. All my costs are in one table(2017,2018,2019) and i made measures for each year. Thats how i got Costs 2018 and Costs 2019

 

In your code the formula expects table columns, but they arent there in my model. they are measures built up like this:

Costs Total = SUM(Data[Costs])

Costs 2019 =  CALCULATE(Costs Total; 'Date'[YEAR] = 2019)

 

In your code: the sum and filter function expect columns

 

If this is the only way to make it work i will have to store seperate costs columns of every year in my model.
 
Is there an option to put year filters in the measures you have created?
Anonymous
Not applicable

Hi @v-xuding-msft 

 

I now have this code and i only need to adjust 1 filter(FILTER(Data;Costs] so that the year is involved

 

Diff = var max_mon = CALCULATE(MAX('Date'[Period]);FILTER(Data;Costs] <> BLANK()))
return
CALCULATE([costs 2018]-[costs 2019];FILTER('Date';'Date'[Period] <= max_mon))
Anonymous
Not applicable

@v-xuding-msft Thanks i will look at it.

 

Another question how do i qet an old unanswered question at the attention of members?

 

My question was registrated with title: Calculate Dossier Active Periods(Self made) and i gave some clarification on my result i am looking for.

adityavighne
Continued Contributor
Continued Contributor

@Anonymous 

create a new column with below if clause -

 

column name = if(cost 2019=blank(), 0, cost 2019)

Anonymous
Not applicable

@adityavighne It works but the totals remain the same.

Any Ideas?

@Anonymous 

no idea. let me check.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.