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

[Youri98]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[Poojara_D12]

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.

[v-hashadapu]

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.

[v-vpabbu]

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

[Youri98]

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. 

[v-vpabbu]

Hi @Youri98,

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

Regards,

Vinay Pabbu

[sjoerdvn]

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.

[Youri98]

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

[bhanu_gautam]

@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
)

[Youri98]

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.

[bhanu_gautam]

@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)
)

[Youri98]

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.