Forum Discussion
Helper IFilter table, convert column from integer to date and +1 day
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.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] + 1This 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.
Super UserHi 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.