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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
crln-blue
Post Patron
Post Patron

IF Statement Date on Measure : date column not showing on options to use for calculation

Hello!

I have a Date column and I need to make a measure which is based on this sample:

crln-blue_0-1606980582376.png

 

The dates are always on a 12-month period. I made a measure for CEI and my goal is for the CEI to have a blank data for the first month (due to insufficient data because my date will always be on a 12-month period). My CEI measure formula is okay except for the first data on the first month, I would like it to be blank. So I tried to make a measure using an if else statement where I'd like to check the Month of my Date column, however, my Date column is not on the list of columns/measures that I use to have computations with.

 

Is there any other approach for this? Thanks in advance!

2 ACCEPTED SOLUTIONS
DataInsights
Super User
Super User

@crln-blue,

 

If the date columns are from the date table (Dates in this example), you could add this to the end of your measure:

 

IF ( MONTH ( MAX ( Dates[Date] ) ) = 1, BLANK (), MeasureValueHere )

 

This assumes the Dates table has a relationship with the data table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

v-stephen-msft
Community Support
Community Support

Hi @crln-blue ,

 

Try this measure

CEI1 = IF(NOT(LEFT(MAX('Table'[Type]),3)="Jan"),MeasureValueHere))

 

Make sure "Show items with no data" has been enabled.

27.png 

 

 

 

 

Best Regards,

Stephen Tao

 

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

4 REPLIES 4
Ramanji
Frequent Visitor

If function is not showing the date column for calcumation, please suggest

crln-blue
Post Patron
Post Patron

Hello to @DataInsights  and @v-stephen-msft !
Thank you for the reply. My first month is being calculated automatically and I just want it to leave it blank. I solved the problem by writing an IF statement because I also have another measure which is blank wherein my CEI measure is dependent to. All your answers are correct and thanks!

v-stephen-msft
Community Support
Community Support

Hi @crln-blue ,

 

Try this measure

CEI1 = IF(NOT(LEFT(MAX('Table'[Type]),3)="Jan"),MeasureValueHere))

 

Make sure "Show items with no data" has been enabled.

27.png 

 

 

 

 

Best Regards,

Stephen Tao

 

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

DataInsights
Super User
Super User

@crln-blue,

 

If the date columns are from the date table (Dates in this example), you could add this to the end of your measure:

 

IF ( MONTH ( MAX ( Dates[Date] ) ) = 1, BLANK (), MeasureValueHere )

 

This assumes the Dates table has a relationship with the data table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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