DAX Formula causing Continuous Values For Future and Past Months

[CracktheCode85]

Hi There! 

I am once again faced with a strange issue.... I have a calculation for a percentage where the Numerator and Denominator are both formulated to stop at the current month. 

I.E. We will see the correct values in a table for the Num and Denom from January to April 2025. 

The issue- when I make this into a percentage and formulate it as such: "%PM = Calculate(1-[Num] / [Denom]) 

I get the correct percentages from Jan to April BUT I also get 100% for May and Beyond.... 

I've tried DATEADD, DATESBETWEEN, and other time functions but none of them seem to work to cut the values off at the current month end (April).

Please help! 

[maruthisp · Accepted solution]

Hi @CracktheCode85 ,

Please find the below DAX expression. It would be good to sahre sample data to work on DAX expression when you are posting. 

%PM =
IF(
NOT( ISBLANK( [Denom] ) ),
1
- DIVIDE( [Num], [Denom] ),
BLANK()
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[CracktheCode85]

Hey Maruthisp! 

That did the trick! The Current Month of MAY still shows 100% but I can work with that. 

I'm not sure I fully understand how or why it worked...  would you mind providing a breakdown of how that formula works with the Blanks? 

[Ashish_Excel]

Hi,

Does this measure work?

Measure = if(isblank([Num]),blank(),divide(([Num]-[Denom]),[Denom]))

Hope this helps.