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
pbi1908
Helper III
Helper III

I am getting a date as a number in a switch measure

Hi all, 

 

I have a matrix with information of my invoices. So in the Rows i have the CUSTOMER_NAME, INVOICE_ID, PAYMENT_ID and then in the values i have many switch measures e.g The Total_invoiced_amount which appeares only in the INVOICE_ID row, the Allocated_amount which appeares only on the PAYMENT_ID row since is the allocated amount for that invoice from a specific payment etc. etc. 

 

I have also the Invoice_date and the Payment_date, in the case of Invoice_date everything works normally, in case of Payment_date instead of get back the date i am getting back a number. Meanwhile the Data Type of the column is Date. I want to avoid to use the Format function because if I use it the performance of my matrix is horrible so when i expand the matrix is taking ages to load while when is number is loading in seconds. 

 

I don't know if affects but in my Paymen Date i have blanks in some cases, i tried to fill the blank with today's date but i am still facing the same problem so i don't think that affects. 

 

Below you can find the switch measure. 

 

MATRIX_RECEIVABLE_DETAILS_PAYMENT_DT = SWITCH(TRUE(),
ISINSCOPE(FT_ALLOCATIONS_BI[PAYMENT_ID]),SELECTEDVALUE(FT_ALLOCATIONS_BI[PAYMENT_DATE]),
ISINSCOPE(FT_ALLOCATIONS_BI[INVOICE_ID]),BLANK(),
ISINSCOPE(DIM_CUSTOMERS_BI[CUSTOMER_NAME]),BLANK())

 

Thank you. 

1 REPLY 1
larsheinemann
Frequent Visitor

Here's a breakdown of the potential problems and how to address them:

  1. Date as a Number: Power BI stores dates as serial numbers, where the integer part represents the date, and the decimal part represents the time. It might be displaying the date in this numeric format.

  2. Blank Values: If there are blank or NULL values in your date column, they might be contributing to the issue.

  3. Data Types: If the PAYMENT_DATE field has a Date data type but is interpreted differently within a measure, it might also cause the issue.

Given your constraints regarding the FORMAT function's performance, you may consider the following:

  1. Check Data Type Consistency: Make sure that the data type for the Payment Date is consistently Date across all elements of your data model.

  2. Handle Blanks Outside the Measure: If possible, handle blanks in your payment date during data preparation in Power Query. You can replace blanks with a specific date if that aligns with your business logic.

  3. Consider a Conditional Column: If possible, you might consider creating a new column that determines the date or other value to be displayed based on your criteria, rather than using a measure. This can sometimes improve performance.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.

Top Solution Authors