Power BI Divide Measure not ignoring the Blank rows

[shubhamamilkant]

Hello, I have spent few hrs on this and I am not sure how to solve this problem,  I want Divide this 2 measures, Rolling 6 month Gross Profit by Total Gross Profit past 12 months, u can see thare are few blanks in both columns, I want ignore this to get a Final value.

Currently as per the image - Divide value is 79.61 (4,132,822/5,191,196)  but if u filterout blank rows  it will be 73,17% (2,844,347/3,887,309), I would like to see 73.17% as a value in card, but no matter what formula i used to divide (like fiter using blank condition, using if condition, it is always showing me 79.61 % ) 

can some one help to write correct formula to get 73.17% in card, In table I can get 73.17% by using the same not blank filter 

Thanks in advanced. 

This is what i am getting - 

This is what i need -  but instad of table in card (73.17)

[themistoklis]

@shubhamamilkant 

Can you share the formula that you use in the card and also in the table?

If you could share the pbix file that would be even better. Do not send any sensitive data

[shubhamamilkant]

When I am trying to reply I am receiving this error, any idea ( i am not able to copy any formula because of this)

[shubhamamilkant]

@themistoklis 
Formula i have used is - 

New GgPR 2021 = DIVIDE([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months]))

another formula i tried is, 
New GgPR 2021 = IF(NOT(ISBLANK([Rolling 6 months Gross Profit])) && NOT(ISBLANK([Total Gross Profit rolling past 12 months])), DIVIDE([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months]))

another one i used,  not the exact syntax but - if(rolling 6 month <> blank(),if(Total Gross profit past 12 month <> blank(), Divide ([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months])
This all 3 formulas gave, exact same result 79.61 %

I am not sure, If i can share Pbix file, but i hope this helps to understand

[themistoklis]

Hello @shubhamamilkant ,

Could you please share the formulas that you use for measures:

Rolling 6 months Gross Profit, Total Gross Profit rolling past 12 months

Also in the table you have only 1 dimension called 'Program/ACCT'?

[shubhamamilkant]

@themistoklis Sorry for the delay in the response 
Let me start again, to give little bit more information - 
I have 2 tables, 1) load_summary Table and 2) Date Table 

1)load_summary Table has 3 columns Program/Acct, load_date and Total Gross Profit 
2) Date table is created by me - 

CALENDAR(MIN(load_summary[load_date]),MAX(load_summary[load_date]))

I want to calculate New GgPR 2021 (which is last 6 month gross Profit Divide by 12 months ago (6 months rolling Profit) 

Assuming Today is Feb 1 - last 6 month Gross profit  will be from 8/2/2020 to Today 
12 months ago (6 months rolling Profit)  = 02/1/2020 to 8/1/2020 (6 months profit but from from 12 months)

Measure Rolling 6 months Gross Profit =  

var first_date_2021 = TODAY()
var end_date_2021 = EDATE(TODAY(),-6)
return
CALCULATE([Total Gross Profit],DATESBETWEEN( 'Date Table'[Date], end_date_2021, first_date_2021))

Measure 
Total Gross Profit rolling past 12 months =
var first_date_2021 = EDATE(TODAY(),-12)
var end_date_2021 = EDATE(TODAY(),-6)
return

CALCULATE([Total Gross Profit], DATESBETWEEN('Date Table'[Date], first_date_2021, end_date_2021))

What I want to calculate is % of gross profit only when we had business (acct/program) in both measures (rolling 6 months / Gross Profit rolling from 12 months to 6)

I really appreciate all the help, I hope this clears my question and  Thanks in advance