topic Re: Filter table, convert column from integer to date and +1 day in DAX Commands and Tips

Filter table, convert column from integer to date and +1 day

Youri98 — Wed, 26 Mar 2025 13:03:22 GMT

Hi,

What I'm trying to do is:

1st step: filter table, this works with the DAX:

Aantal aansluitend verhuurd =

CALCULATE(

DISTINCTCOUNT('FEIT_Verhuur'[%ContractKey]),

FEIT_Verhuur[WAARDEGETAL] = 1.00,FEIT_Verhuur[Subonderwerp] IN { "Days" })

2nd step: When I do this, there's a column that consists of the date, but it's an integer type, '20250131' for example. So I would like to convert it to a date type (with a DAX).

3rd step: After doing this, I would like to add +1 day to the date. Because I would like to show the dates that are at the end of the month in the next month (e.g. 20250131 = February, 20250228 = March).

Would this be possible to do?

Thanks in advance.

Re: Filter table, convert column from integer to date and +1 day

bhanu_gautam — Wed, 26 Mar 2025 13:11:43 GMT

Youri98

You can use the DATE function to convert the integer date to a date type. Assuming your integer date is in the format YYYYMMDD, you can use the following DAX formula:

dax
ConvertedDate =
DATE(
INT(LEFT(FEIT_Verhuur[IntegerDate], 4)),
INT(MID(FEIT_Verhuur[IntegerDate], 5, 2)),
INT(RIGHT(FEIT_Verhuur[IntegerDate], 2))
)

Once you have the date in the correct format, you can add one day to it using the DATEADD function:

dax
NextMonthDate =
DATEADD(
DATE(
INT(LEFT(FEIT_Verhuur[IntegerDate], 4)),
INT(MID(FEIT_Verhuur[IntegerDate], 5, 2)),
INT(RIGHT(FEIT_Verhuur[IntegerDate], 2))
),
1,
DAY
)

Re: Filter table, convert column from integer to date and +1 day

Youri98 — Wed, 26 Mar 2025 13:17:58 GMT

So, I can do both in 1 DAX directly, right? And how would I use this date in combination with the filters I used in the different DAX.

Re: Filter table, convert column from integer to date and +1 day

sjoerdvn — Wed, 26 Mar 2025 13:19:19 GMT

Well, you could convert the number to a string (FORMAT) and then to a date (DATEVALUE), but I do not recommend doing this in DAX at all. You're better of doing this sort of thing in Power Query, or even your data source.

Re: Filter table, convert column from integer to date and +1 day

bhanu_gautam — Wed, 26 Mar 2025 13:20:49 GMT

Youri98 You can combine using var

dax
Aantal aansluitend verhuurd =
CALCULATE(
DISTINCTCOUNT('FEIT_Verhuur'[%ContractKey]),
FEIT_Verhuur[WAARDEGETAL] = 1.00,
FEIT_Verhuur[Subonderwerp] IN { "Days" },
VAR ConvertedDate = DATE(
INT(LEFT(FEIT_Verhuur[IntegerDate], 4)),
INT(MID(FEIT_Verhuur[IntegerDate], 5, 2)),
INT(RIGHT(FEIT_Verhuur[IntegerDate], 2))
)
RETURN DATEADD(ConvertedDate, 1, DAY)
)

Re: Filter table, convert column from integer to date and +1 day

Youri98 — Wed, 26 Mar 2025 13:23:41 GMT

Thanks for the reply, if this was an option I would do it as well. But this is not possible in this situation.

Re: Filter table, convert column from integer to date and +1 day

Youri98 — Wed, 26 Mar 2025 13:29:13 GMT

I get this error: A single value for column 'DATUM' in table 'FEIT_Verhuur' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Re: Filter table, convert column from integer to date and +1 day

Anonymous — Thu, 27 Mar 2025 10:14:41 GMT

Hi Youri98,

Thank you for reaching out to Microsoft Fabric Community Forum.

After multiple workarounds, I found that the IntegerDate column should be in text format. If it's stored as a whole number, Power BI misinterprets the data type, leading to incorrect results.

Create a new Calculated column for converting IntegerDate to a date:

ConvertedDate = VAR DateText = FORMAT(Sheet1[IntegerDate], "00000000") -- Ensures 8-digit format RETURN DATE( LEFT(DateText, 4), -- Extract Year MID(DateText, 5, 2), -- Extract Month RIGHT(DateText, 2) -- Extract Day )

Create another new column to add +1 day:

NextDay = Sheet1[ConvertedDate].[Date] + 1

Here is the output in DD/MM/YYYY format:

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

Re: Filter table, convert column from integer to date and +1 day

Youri98 — Thu, 27 Mar 2025 10:57:44 GMT

Thank you for the reply. As we don't manage the data set I can't add columns myself so that's why I wanted to see if it would be possible with DAX. But I got multiple messages now saying that it's not possible with DAX. Thanks for trying to help. I guess I'll just have to get back to the owners of the data set and see if they can add the columns for me.

Re: Filter table, convert column from integer to date and +1 day

Anonymous — Mon, 07 Jul 2025 11:12:59 GMT

Hi Youri98,

Thanks for the update. I hope that solves your issue. Please share the details here after it is solved.

Regards,

Vinay Pabbu

Re: Filter table, convert column from integer to date and +1 day

v-hashadapu — Wed, 09 Jul 2025 09:41:25 GMT

Hi , Thank you for reaching out to the Microsoft Community Forum.

I have reproduced your scenario in Power BI Desktop and successfully implemented a solution where we built a DAX measure that counts distinct contracts from the FEIT_Verhuur table where WAARDEGETAL = 1.00 and Subonderwerp = "Days", and shifts the DateInt value forward by one day to get NextDay. A disconnected MonthTable slicer allows users to filter based on the month of this NextDay. The measure updates visuals like a card to show how many contracts roll over into the selected month, such as showing February results when DateInt is 20250131.

For your reference, I’ve attached the working .pbix file.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Re: Filter table, convert column from integer to date and +1 day

Poojara_D12 — Mon, 07 Jul 2025 12:35:19 GMT

Hi Youri98

Yes, what you're trying to do in Power BI is definitely possible using DAX, and it can be broken down into three clear steps. In the first step, you've already correctly filtered your data using a CALCULATE expression to count distinct contract keys based on conditions like WAARDEGETAL = 1.00 and specific Subonderwerp values. In the second step, since your date is stored as an integer in the format YYYYMMDD (e.g., 20250131), you can convert it to a proper date using the DATE() function in DAX. This involves extracting the year, month, and day using basic arithmetic. For example, you can create a calculated column with:

ConvertedDate = DATE( DIVIDE('FEIT_Verhuur'[DateInt], 10000), MOD(DIVIDE('FEIT_Verhuur'[DateInt], 100), 100), MOD('FEIT_Verhuur'[DateInt], 100) )

This will transform an integer like 20250131 into a true date value. In the third step, you can then add 1 day to this new date using +1, like this:

ShiftedDate = 'FEIT_Verhuur'[ConvertedDate] + 1

This effectively moves end-of-month dates (e.g., January 31) into the next month (e.g., February 1), which is useful when you want to report activities as occurring in the following month. So yes, by using DAX in calculated columns, you can convert the integer to a date and shift it forward by one day for your reporting logic.

Re: Filter table, convert column from integer to date and +1 day

v-hashadapu — Thu, 10 Jul 2025 13:03:31 GMT

Hi Youri98 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

Re: Filter table, convert column from integer to date and +1 day

v-hashadapu — Mon, 14 Jul 2025 12:38:21 GMT

Hi Youri98 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

Re: Filter table, convert column from integer to date and +1 day

v-hashadapu — Thu, 17 Jul 2025 11:05:17 GMT

Hi Youri98 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.